sql question

[ruijorge]

Hi,

I am building some tables and most of them have a unique 'Serial number' field with type varchar(50). As it is unique for every component it should be the primary key along with Date because I might insert the same component on the same table more than once and I want to record all.

So this is what I have:

sNumber varchar(50) primary key,
Date datetime primary key,
...
...


My question is:
Should I instead add an ID field to the table with auto-increment on? The table would become:

ID int primary key(auto-increment on),
sNumber varchar(50),
Date datetime,
...
...

What would you do?

Thanks!

 

[imported_Dhaval00]

Depending on how the DB will be used, and the volume of data, having an auto-incrementing int column is the way to go. I bring up the volume issue, because instead of int, you may want to use a bigint. Nevertheless, bigint cells are 8KB in size (as opposed to int's 4KB). Main point being, always have surrogate keys... they are easier to manipulate, your clients need not know anything about them, and indexing is easier (not to mention, your clustered index will probably end up being be smaller in size).

Also, the first example, where your date is the PK, is a bad one - your business layer may not have the precise control over the DateTime values and you could easily end up with multiple rows with the same time. Usually, in data warehousing, people like to use SQL Server's timestamp, which is just a float and is guaranteed (I believe) to be unique.

 

[ruijorge]

Thanks for the suggestions.

 

[kamper]

Also, if you go with the separate sequence as primary key (which I think is a good idea for all of dhaval's reasons and more) but still want to guarantee a unique combo of serial number and date you can set them to be an unique key. Just make sure you pair them so that each doesn't have to be unique on its own. I certainly don't know for sure, but I imagine this would be a speed killer if you do a lot of inserts on this table. The db would have to keep a unique index updated (or risk perpetual full table scans).