Identity and Primary Keys

Question

Identity and Primary Keys

Digs

SSCertifiable

Points: 7333
More actions
February 26, 2005 at 1:18 pm

#185377

First, when typing this post, how do I stop the text in my post from having such a wide gap between paragraphs.
My table fields : ID, Customer Code, Customer Names
ID is a sequential number ( @@Identity, etc)
Customer code will be a unique customer number, no duplicates, Customer name my be duplicates, as customer code may have two customers names with the same name ( ie two accounts, different reasons).
Questions:
1) Why cant the Customer code be the Primary key, why would you make ID primary key, when customer code is main data field ?
2) Identity field, what if you have 100,000,000 records ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

michanne SSCrazy Points: 2329 More actions · Answer 1

There is no rule that you primary key must be an identity field. An int is faster to deal with but if it isnt convenient to use that isnt going to make much difference.

If you have that many records maybe an alphnumeric column is better. At least with an alphanumeric col you can possibly tell something about the record, and decimal can't tell you much at all.

Sanjay Masawan Ten Centuries Points: 1031 More actions · Answer 2

To avoid large gaps between the paragraphs while typing

Press: Shift + Enter

instead of just Enter

Sanjay Masawan Ten Centuries Points: 1031 More actions · Answer 3

Why dont you use UniqueIdentifier instead of identity.

UniqueIdentifier is recomended by most of the techies

Digs SSCertifiable Points: 7333 More actions · Answer 4

UniqueIdentifier

Thats my point, the customer number must be unique, there can be no duplicates, so why is that not a type of " UniqueIdentifier", and use that as primary key. So I thought that an ID key would be little use, unless one needs a method of working out if a new row has been added.

athurgar SSCrazy Points: 2816 More actions · Answer 5

You don't need the ID column as the customer code as a primary key will enforce unqiueness and not null.

You can make an identity column larger - just change it to a big int.

Also unique identifiers should only be used in special circumstances - read BOL Using uniqueidentifier Data

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Concur... a world of hurt lays ahead for those using Unique Identifiers in a casual manner.

... and, I agree, the customer code is the unique number to be used as the primary key and you shouldn't even need an ID column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

UniqueIdentifier is recomended by most of the techies

Curious, what techies you have in mind here?

As for the thread: athurgar already hit the nail right on the head. the additional ID seems pretty redundant.

And to piggyback on athurgar. If even bigint isn't enough for you , you can make it DECIMAL(38,0). And if you know in advance that you need to store that much rows, you can use something like:

create table decimal_t

(

col1 decimal(38,0) identity(-99999999999999999999999999999999999999, 1)

)

Well, it doesn't look nice to have a negative customer number, but it does the job.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]