Showing posts with label unique. Show all posts
Showing posts with label unique. Show all posts

Monday, March 26, 2012

making columns unique

We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
BrandonThere's no big difference between a unique index and a unique constraint.
When you create the unique constraint, a unique index gets created in the
background. However, if you want a foreign key to use that table, you must
use a primary key or unique constraint - a unique index by itself isn't
sufficient.
The above are sufficient to enforce uniqueness. There is no reason to use a
trigger here. Also, don't worry about the size of the table. Much large
tables have been built without problems.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon|||I would not use triggers. Triggers have more overhead to OLTP applications.
Unique constraint should take care of the issue. 3 column 6 columns should
not be a bit concern with the size/groth you are looking for.
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
> We have a table in our database that has 6 columns that as a whole are not
> indexed together however we would like for them to be unique. Would it be
> more efficent or better to create a unique constraint...or a unique
> index...or to create a insert trigger that would perform a look up for a
> record with the same values you are wanting to insert. It would do this
> using an index on 3 of the 6 fields that would not always narrow it down
to
> one row. I am asking this because there is some concern in the office
that
> adding a 6 column index on this table would add a good bit of burdern and
> adding 3 columns to the existing index wouldn't be a whole lot better.
This
> table could contain 100s of 1000s of records. What would you recommend.
> Thanks,
> Brandon
>sql

making columns unique

We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon
There's no big difference between a unique index and a unique constraint.
When you create the unique constraint, a unique index gets created in the
background. However, if you want a foreign key to use that table, you must
use a primary key or unique constraint - a unique index by itself isn't
sufficient.
The above are sufficient to enforce uniqueness. There is no reason to use a
trigger here. Also, don't worry about the size of the table. Much large
tables have been built without problems.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon
|||I would not use triggers. Triggers have more overhead to OLTP applications.
Unique constraint should take care of the issue. 3 column 6 columns should
not be a bit concern with the size/groth you are looking for.
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
> We have a table in our database that has 6 columns that as a whole are not
> indexed together however we would like for them to be unique. Would it be
> more efficent or better to create a unique constraint...or a unique
> index...or to create a insert trigger that would perform a look up for a
> record with the same values you are wanting to insert. It would do this
> using an index on 3 of the 6 fields that would not always narrow it down
to
> one row. I am asking this because there is some concern in the office
that
> adding a 6 column index on this table would add a good bit of burdern and
> adding 3 columns to the existing index wouldn't be a whole lot better.
This
> table could contain 100s of 1000s of records. What would you recommend.
> Thanks,
> Brandon
>

Friday, March 23, 2012

Making an Existing column an Identity column

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)Nope...can't do it...

If you have a table with a int column and want to change it...you can graphically do it in EM...

But guess what it's really doing...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1a
(
col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a ON
GO
IF EXISTS(SELECT * FROM dbo.Table1a)
EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
SELECT col1 FROM dbo.Table1a TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
GO
DROP TABLE dbo.Table1a
GO
EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
GO
GRANT SELECT ON dbo.Table1a TO public AS dbo
COMMIT

Just make sure you don't already have a temp_table...

What a hack

M$ is good at them...lots of practice...|||Look it up in BOL

TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.

And the IDENTITY_INSERTS Are inb the correct order...

btw, sql server itself generated it...look a save sql server change script in EM after you make a change to a table...sql

Making a 'sort-of' unique ID field

(I posted this in another newsgroup and it was recommended to me that I post
in here)
OK, that's an odd subject line. Let me try to explain what we have in our
table:
item language collectionID GUID
the form1 english 1 5
el form1 spanish 1 6
la form1 french 1 7
the form2 english 2 8
el form2 spanish 2 9
la form2 french 2 10
the collection ID is a way to group records into a 'set'. Now, normally, I'd
create another table called 'collections' and then relate that to this
table. So, to add a form, you'd first create a collection, then you could
add a form.
However, the developer I work with prefers this methodology of only having
one table, just to keep the number of tables and JOINS manageable (We have a
ton of tables...)
I'm trying to figure out if this is doable. The catch is making a new
collection ID and ensuring that it will be unique. I could obviously query
the DB by grabbing the top1 collectionID and then just increment it by one.
The catch is if two people are trying to add a new collection at the same
time. There's a chance that they'd both query the DB at the same time and
then attempt to make new, separate, collections, but then end up with them
sharing the same ID.
Is there a way to trigger a 'uniqueID' on a field like this on the SQL send
instead of first having to do a query look up and manually creating the
'unique' ID for this field?
Of is this just the wrong way to do it and go back to using multiple tables?
The one solution I've been given is to do the lookup, then an imediate
insert to create the new collection ID.
So, if a person clicks on NEW COLLECTION in the app, the following would
happen:
1) query the DB and request the TOP 1 collectionID
2) increment that number by 1 and then do an immediate insert of new record
into the DB
3) immediately requery the DB to grab the record with this new collectionID
and grab its GUID
4) present the form, allow the end-user to enter all the data
5) do a final UPDATE on the record with the GUID I pulled from step 3
Doable? Bad idea? Alternatives?
-DarrelWhy not store the data in two separate tables, but add a View that performs
the underlying join for you? Then you can update your individual tables,
and the other guy gets his single-table view that he likes so much... A
little something for everybody in there somewhere...
"darrel" <notreal@.hotmail.com> wrote in message
news:eBOFR1HXFHA.3320@.TK2MSFTNGP12.phx.gbl...
> (I posted this in another newsgroup and it was recommended to me that I
> post
> in here)
> OK, that's an odd subject line. Let me try to explain what we have in our
> table:
> item language collectionID GUID
> the form1 english 1 5
> el form1 spanish 1 6
> la form1 french 1 7
> the form2 english 2 8
> el form2 spanish 2 9
> la form2 french 2 10
> the collection ID is a way to group records into a 'set'. Now, normally,
> I'd
> create another table called 'collections' and then relate that to this
> table. So, to add a form, you'd first create a collection, then you could
> add a form.
> However, the developer I work with prefers this methodology of only having
> one table, just to keep the number of tables and JOINS manageable (We have
> a
> ton of tables...)
> I'm trying to figure out if this is doable. The catch is making a new
> collection ID and ensuring that it will be unique. I could obviously query
> the DB by grabbing the top1 collectionID and then just increment it by
> one.
> The catch is if two people are trying to add a new collection at the same
> time. There's a chance that they'd both query the DB at the same time and
> then attempt to make new, separate, collections, but then end up with them
> sharing the same ID.
> Is there a way to trigger a 'uniqueID' on a field like this on the SQL
> send
> instead of first having to do a query look up and manually creating the
> 'unique' ID for this field?
> Of is this just the wrong way to do it and go back to using multiple
> tables?
> The one solution I've been given is to do the lookup, then an imediate
> insert to create the new collection ID.
> So, if a person clicks on NEW COLLECTION in the app, the following would
> happen:
> 1) query the DB and request the TOP 1 collectionID
> 2) increment that number by 1 and then do an immediate insert of new
> record
> into the DB
> 3) immediately requery the DB to grab the record with this new
> collectionID
> and grab its GUID
> 4) present the form, allow the end-user to enter all the data
> 5) do a final UPDATE on the record with the GUID I pulled from step 3
> Doable? Bad idea? Alternatives?
> -Darrel
>
>
>|||> Why not store the data in two separate tables,
> but add a View that performs
> the underlying join for you?
Interesting option. Thanks!
-Darrel|||What is the entity represented by collection ID? How does it make sense
to allocate an arbitrary ID to a "collection" and then not store any
other information about it? Are you saying that a "collection" is
identifiable only by its members and not by any other information (such
as a title, user name, code or other context)? If that is truly the
case then you could always use the key of the first member assigned to
the collection and give the table a self-referencing key. Don't use
IDENTITY for a self-referencing key BTW.

> the developer I work with prefers this methodology of only having
> one table, just to keep the number of tables and JOINS manageable
That sounds ominous. Designing a relational schema on the principle of
reducing the number of joins is a recipe for poor performance and poor
integrity.
David Portas
SQL Server MVP
--|||> However, the developer I work with prefers this methodology of only having
> one table, just to keep the number of tables and JOINS manageable (We have
a
> ton of tables...)
This is where a book from one of the guys behind the relational model come
into play: INTRODUCTION TO DATABASE SYSTEMS by Date. It's a text book, so yo
u
can get an old edition for like $5.
Celko's DATA AND DATABASES is good too and has some practical info.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis|||> What is the entity represented by collection ID? How does it make sense
> to allocate an arbitrary ID to a "collection" and then not store any
> other information about it?
Not sure what other information it needs.
The table stores a variety of forms. Each form may come in different
variations (based on language and some other things). The collectionID is
merely to tie those items together in the absence of any other unique
identifier for that group.

> Are you saying that a "collection" is
> identifiable only by its members and not by any other information (such
> as a title, user name, code or other context)?
Pretty much. We could add more information, but it's not necessarily needed.
Like I said, I'd normally do that myself. I'd make a collections table, add
the ID there, and probably a title (even if just for admin use).
But I was encouraged to avoide the extra tables if possible, and I'm just
trying to figure out if that's a practical request or not.

> If that is truly the
> case then you could always use the key of the first member assigned to
> the collection and give the table a self-referencing key. Don't use
> IDENTITY for a self-referencing key BTW.
Hmm...OK, I think I see what you are saying.
So, I'd remove collection ID and add two new fields:
item language uniqueCollectionStart CollectionID
the form1 english 1 NULL
el form1 spanish 2 1
la form1 french 3 1
Hmm...yea, that would work. The english form becomes the 'parent' of the
collection and the other's relate to it that way.
But I do think things are leaning towards making a separate table. Which I'm
fine with, personally. ;o)
-Darrel|||> Each form may come in different
> variations (based on language and some other things). The
collectionID is
> merely to tie those items together in the absence of any other unique

> identifier for that group.
I still don't see how the application or the user is supposed to know
which group is which if the only identifying information is an
artficially generated number. "I created a collection last month - I
think it was 7239. Or was it 7293...?" Usually, exposed surrogate keys
come with other information to identify them in the data model -
otherwise there is no obvious point to them.

> So, I'd remove collection ID and add two new fields:
Why add a new column? Doesn't your table have a key already?
David Portas
SQL Server MVP
--|||> I still don't see how the application or the user is supposed to know
> which group is which if the only identifying information is an
> artficially generated number.
Hmm...well, I could just query the DB and only pull up english records
which, in theory, would be unique items. But yea, I'm seeing that this is
not an ideal way to go about it.

> Why add a new column? Doesn't your table have a key already?
It does, but you had mentioned NOT using the unique identifier...though I
admit I wasn't sure why.
-Darrel|||>> However, the developer I work with prefers this methodology of only
having
one table, just to keep the number of tables and JOINS manageable (We
have a
ton of tables...) <<
First of all, never design a database for the purpose of front end
display and "easier" application coding. You design a database to be
consistent and to maintain data integrity. You do not cross tiers in a
tiered archtecture. This is more fundamental than SQL.
Is this a OTLT problem? I hope people have gotten over that design
flaw. You also keep talking about records, fields and GUIDs which have
nothing to do with a relational model.
If you are looking for a way to do translations, try this approach:
CREATE TABLE Items
(english VARCHAR(20) NOT NULL PRIMARY KEY,
spanish VARCHAR(20) NOT NULL,
french VARCHAR(20) NOT NULL);
Then add the right collations to spanish and french. Create VIEW for
each language.|||> Is this a OTLT problem?
Not really. I'm not even sure if it's a problem.
I'm by no means a DB expert, and my gut reaction is to always build two
tables when I need to do this sort of grouping. One table to create the
groups, that then relates to the table of individual records.
I wasn't sure if there was an equally viable solution using only one table.
And, from the responses, it seems like any solution is really only a hack,
and not ideal.
As such, I think I'm going to go back to the tried-and-true two-table
method. ;o)

> If you are looking for a way to do translations, try this approach:
> CREATE TABLE Items
> (english VARCHAR(20) NOT NULL PRIMARY KEY,
> spanish VARCHAR(20) NOT NULL,
> french VARCHAR(20) NOT NULL);
I think that's what David was describing. That basically makes the English
version the parent. Of course, while it's likely there'll always be an
English version, it's not guaranteed...which goes back to being perhaps
another argument to just go with two tables.
-Darrel

Making a Non Primary key a unique column

I have a table in which a column that is not part of the primary key must
contain data that can not be duplicated in the same column in another row
but it should not - for other design reasons - be the primary key.
I created an index on that field in the table and in the properties for that
index I checked Create Unique and checked Constraint. I thought that that
would prevent entering duplicate values in that field in that table.
However, when I tested this in the table in the data entry screen of
Enterprise manager, I was able to enter duplicate values in that field in
several rows in that table and the database did not return any error
messages.
What's wrong here, can anyone shed light on this behaviour?
How do I achieve the goal set out above?
The field giving me the problem is an nvarchar type max 50 length.
Thanks for any help,
RDIt is difficult for us to answer without knowing exactly what you did and ho
w we can reproduce it.
Can you post CREATE TABLE, CREATE INDEX or ALTER TABLE ADD UNIQUE CONSTRAINT
with some insert
statements we can run to reproduce the behavior?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RD" <nospam@.nospam.net> wrote in message news:%23hgdDzSaFHA.2996@.TK2MSFTNGP10.phx.gbl...[c
olor=darkred]
>I have a table in which a column that is not part of the primary key must
> contain data that can not be duplicated in the same column in another row
> but it should not - for other design reasons - be the primary key.
> I created an index on that field in the table and in the properties for th
at
> index I checked Create Unique and checked Constraint. I thought that that
> would prevent entering duplicate values in that field in that table.
> However, when I tested this in the table in the data entry screen of
> Enterprise manager, I was able to enter duplicate values in that field in
> several rows in that table and the database did not return any error
> messages.
> What's wrong here, can anyone shed light on this behaviour?
> How do I achieve the goal set out above?
> The field giving me the problem is an nvarchar type max 50 length.
> Thanks for any help,
> RD
>
>[/color]|||A UNIQUE constraint should ineed prevent duplicate values. I suspect the
constraint has not been created as you wanted it. In Query Analyzer you can
easily generate the script for the constraint so that you can verify it and
edit it as necessary (right-click on the constraint in the Object Browser,
then click Script Object to New Window As > Create).
One reason I prefer to use QA rather than EM for any structure changes is
that you have better control and visibility over what is happening. However,
you can do a similar thing in EM when you change something in the Table
Designer. You can click the Save Change Script button on the toolbar (3rd
one along) to show you the actual script that will make the changes. The
complete change script EM generates is harder to read however than the
equivalent in QA.
Most of us will be glad when the EM/QA duality disappears in SQL2005 to be
replaced by a single place for all management and development tasks.
David Portas
SQL Server MVP
--|||You might want to create the table in QA with DDL and use the UNIQUE
constraint. This will document your design better. I have no idea why
EM would not do this properly.|||Thanks to all for your explanations.
Indeed it works properly as explained by you and the docs.
This morning I just tried again and realized that the duplicate data I
thought I entered was not EXACTLY duplicate after all, hence my mistaken
belief that it didn't work.
As usual the problem is 18 inches from the screen.
Sorry to have disturbed you like that, comes with old age I guess. Can't
stay up late anymore and do anything worthwhile ;-)
RD.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117914232.752590.247840@.g49g2000cwa.googlegroups.com...
> You might want to create the table in QA with DDL and use the UNIQUE
> constraint. This will document your design better. I have no idea why
> EM would not do this properly.
>

Wednesday, March 21, 2012

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!
http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>
|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>
|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.c..._duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.c..._duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>>I have an INTEGER column that I want to convert to a primary key. However,
>>some of the values currently in the dataset are not unique. What query
>>will delete any records with duplicate values of the intended index?
>> Many thanks!
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Make varchar field unique

Anybody know a way to make a varchar field not allow duplicates besides
making it a primary key?hi adam,
how about creating unique constraint on the column.
Ex:
create table #t(col1 varchar(50) unique)
insert into #t values('a')
insert into #t values('b')
insert into #t values('a') -- error
-- Vishal

Monday, March 12, 2012

Make a field unique

How can i modify a field in SQL SERver 2005 to make it unique?

ALTER TABLE myTableName
ADD CONSTRAINT constraintName
UNIQUE (myField)

?