Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-07-25 : 09:01:23
|
| ee writes "I am building a primary key for a table that is based on a prefix(based on the users office) and a number that is incremented by one each timeIs it better to select the maximum number each time and add 1 or is it more economical to save a counter in a table that is updated each time a record is inserted(thus saving the max query)?" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-25 : 09:08:08
|
| Max() + 1 is not a good approach if u are seriously considering multi-user app b'coz by the time u fetch max(), somebody may insert some record which will break your max()+1 logic..better go for auto-increment identity column.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-25 : 09:09:01
|
| Can you use an IDENTITY instead?The MAX query will be slow if the table has many rows (perhaps not if that column is indexed), the table-lookup-query will be a bottleneck and cause blocking (and you have to be careful that you get your transaction blocks correct so that two people cannot use the same ID number.One good thing about the table-lookup approach is that you can increment by MORE than 1 - so if you have 100 rows in a temporary table you can increment the NextID table by 100, and allocate that [b]block[p/b] of numbers to your rows as you insert them.Kristen |
|
|
|
|
|
|
max vs.counter