January 6, 2009 at 11:07 am
We have a table which will be subject to large volumes of INSERTs from web transactions - e.g. several per second at busy periods of the day. We need to define a unique PK for the table, and the obvious options would be:
1. Use a GUID
2. Use an Identity column.
Does anyone have any feedback on which of these two approaches would be preferable, or what other factors we'd need to consider?
We're running SQL Server 2005 Std on Windows 2003 Server Enterprise.
January 6, 2009 at 11:10 am
What is the current table definition? Maybe there's a valid natural key in there.
January 6, 2009 at 11:39 am
for pure insert purpose, identity column wouldn't be a big overhead.
however, whether to make it a clustered index depends on other factors such as data distribution and how you are going to query the table, etc.
January 6, 2009 at 11:41 am
If there's not a valid natural key in there, you really haven't designed the table correctly.
January 6, 2009 at 12:41 pm
Thanks for the feedback so far. I was curious as to whether the use of an Identity column for the PK would result in a possible bottleneck when several transactions were attempting to allocate a new unique value at the same time?
January 6, 2009 at 12:46 pm
It's really hard to say without knowing about the volume of transactions you're dealing with. But why would you want to use the identity as your PK? How does the system know when it needs to allocate a new one without referencing an already existing unique key?
January 6, 2009 at 2:21 pm
Throughput is about 50k transactions per day. Each requires a new unique key.
January 6, 2009 at 2:29 pm
regardless of how you define the rest of your table, an identity column wouldn't be a problem for 50k per day.
January 6, 2009 at 2:37 pm
sqlservercentral (1/6/2009)
Thanks for the feedback so far. I was curious as to whether the use of an Identity column for the PK would result in a possible bottleneck when several transactions were attempting to allocate a new unique value at the same time?
No, it will not be a problem. SQL Server handles allocation of identity values internally, so there is no bottleneck waiting for a transaction to complete.
January 7, 2009 at 5:38 am
And btw, most servers can handle 50K insert in less than 1 sec. So in a day will not be an issue, ever.
January 7, 2009 at 10:11 pm
sqlservercentral (1/6/2009)
We have a table which will be subject to large volumes of INSERTs from web transactions - e.g. several per second at busy periods of the day. We need to define a unique PK for the table, and the obvious options would be:1. Use a GUID
2. Use an Identity column.
Does anyone have any feedback on which of these two approaches would be preferable, or what other factors we'd need to consider?
We're running SQL Server 2005 Std on Windows 2003 Server Enterprise.
U should have to choose identiy...
January 8, 2009 at 3:48 am
sqlservercentral (1/6/2009)
We have a table which will be subject to large volumes of INSERTs from web transactions - e.g. several per second at busy periods of the day. We need to define a unique PK for the table, and the obvious options would be:1. Use a GUID
2. Use an Identity column.
Does anyone have any feedback on which of these two approaches would be preferable, or what other factors we'd need to consider?
We're running SQL Server 2005 Std on Windows 2003 Server Enterprise.
If it's a web application, you'd choose GUID. Certainly, most web development frameworks I've seen much about work on this basis, this is what's generated on the client machine to uniquely ID the session/transaction - and so is your natural key. However, if you do use the GUID as your primary key, make sure, and I'm sorry for shouting, but "DO NOT USE IT, OR ANY GUID COLUMN, EVER, AS YOUR CLUSTERED INDEX". Use something like insert date for that (depending upon your design, obviously).
If you have a clustered index on something 'random' like a GUID - you're going to start hitting horrific performance problems with table locking quite sharpish.
January 8, 2009 at 8:03 am
Yup been there, done that. Don't put the clustered index on the GUID, ever.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply