October 27, 2010 at 4:37 am
Hi,
I have an application that periodically inserts a record into a table.
This table have 100 columns, C1 .. C100 of real type and other additional column named 'Time' of datetime type.
The column 'Time' identifies the insert time and uniquely identifies each record.
Which would be better define 'Time' column as the primary key or should define a new aditional column 'regid' (numeric type)
as primary key? what are the pros and cons?
Many thanks in advance.
October 27, 2010 at 4:45 am
Are you absolutely and completely sure that you will never have two rows inserted at exactly the same time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2010 at 4:47 am
Yes, i'm sure.
October 27, 2010 at 5:06 am
the pros are that you will have a nice sequential index that will fill up without causing splits
the cons are that if on the off-chance two records get inserted at the same time then you will have a problem, also the index take up slightly more space than an INT.
I would apply the usual rules around primary key selection and table desing when doing relational modelling and then see if your time key fits under that critera and if so use it.
October 29, 2010 at 12:55 am
Ok, thanks for your help.
October 29, 2010 at 7:55 am
Also if you use your datetime column is the primary key and you have a child table, you will end up with a datetime column as a foreign key. That is really nasty.
Just my 2¢ but I would think about changing the name from "Time" to something that gives an indication about what it is, like "AddTime" or something along those lines.
Also, you WILL end up at some point with multiple records being inserted at the same time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2010 at 8:18 am
Business rules change. Always code it as if in a month the process will start inserting two records at the exact same time.
October 29, 2010 at 8:23 am
tmitchelar (10/29/2010)
Business rules change. Always code it as if in a month the process will start inserting two records at the exact same time.
I agree with this to a certain extent, but there must a limit to the change in business rules that can be anticipated in design. Otherwise natural primary keys would never exist.
October 29, 2010 at 8:30 am
steveb. (10/29/2010)
tmitchelar (10/29/2010)
Business rules change. Always code it as if in a month the process will start inserting two records at the exact same time.I agree with this to a certain extent, but there must a limit to the change in business rules that can be anticipated in design. Otherwise natural primary keys would never exist.
I don't think this is a case of business rules changing so much as it would be a script that needs to be run to insert so missing data.
ex:
insert MyTableWithTimeStampAsPrimaryKey select top 25 [columns] from TableThatDidNotGetInsertedCorrectly
oops now we have 25 records being inserted at the same time. Using the system date of insertion as the primary key WILL have collision at some point. It is a matter of when not if in this case.
I agree with your point about candidate (natural) keys but this does not fall into that category.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply