Identity and Primary Keys
-
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 ?
-
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.
-
To avoid large gaps between the paragraphs while typing
Press: Shift + Enter
instead of just Enter
-
Why dont you use UniqueIdentifier instead of identity.
UniqueIdentifier is recomended by most of the techies
-
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.
-
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
-
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)
Intro to Tally Tables and Functions -
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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply