CODEWITHSUNDEEP
c#sql-server
Michael A
Michael A

Reputation: 5840

Approach for primary key generation

What is the best approach when generating a primary key for a table?

That is, when the data received by the database is not injective and can't be used as a primary key.

In the code, what is the best way to manage a primary key for the table rows?

Thanks.

Upvotes: 1

Views: 307

Answers (3)

Steve Stedman
Steve Stedman

Reputation: 2672

First recommendation stay away from uniqueidentifier for any primary key. Although it has some interesting easy ways to generate it client side, it makes it almost impossible to have any idexes on the primary key that may be useful. If I could go back in time and ban uniqueidentifiers from 99% of the places that they have been used, this would have saved more than 3 man years of dba/development time in the last 2 years.

Here is what I would recommend, using the INT IDENTITY as a primary key.

create table YourTableName(
   pkID int not null identity primary key,
   ... the rest of the columns declared next.
)

where pkID is the name of your primary key column.

This should do what you are looking for.

Upvotes: 2

huhu78
huhu78

Reputation: 389

IDENTITY in SQL Server

and if you need to get know what you new ID was while INSERT-ing data, use OUTPUT clause of INSERT statement - so the copy of new rows is put to table-type param.

If for some reason generating unique ID at SQL is not suitable for you, generate GUID's at your app - GUID has a very hight level of uniquness (but it's not guaranteed in fact). And SQL Server has dedicated GUID type for column - it's called uniqueidentifier.

http://msdn.microsoft.com/en-us/library/ms187942.aspx

Upvotes: 1

nothrow
nothrow

Reputation: 16168

AUTO_INCREMENT in mysql, IDENTITY in SQL Server..

Upvotes: 2

Related Questions