August 4, 2011 at 4:11 am
Hi,
We are currently in the process of designing a new physical database model which will be hosted on Sql Server 2008 R2.
I'm currently putting a lot of thought into whether or not to set the Identity value to "Yes" for each of the Primary Key columns in the tables vs a stored procedure/function which would work out the last entry and increment by one. The reason I'm questioning this is because the stored procedure which inserts information into the associated FK table will have to work out the PK value anyway.
Any thoughts/comments would be much appreciated.
www.sqlAssociates.co.uk
August 4, 2011 at 5:23 am
If you're programmatically determining the key value(s), then no, having identity values isn't going to help that I can see.
Now, how you're determining that value could lead to issues. Are you using uniqueidentifier?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2011 at 5:50 am
Chris Kitchen (8/4/2011)
I'm currently putting a lot of thought into whether or not to set the Identity value to "Yes" for each of the Primary Key columns in the tables vs a stored procedure/function which would work out the last entry and increment by one.
There are many ways to do that wrong (causing accidental duplication or severe performance degradation) and very few ways to do it right.
the stored procedure which inserts information into the associated FK table will have to work out the PK value anyway.
Scope_identity or the output clause. Let SQL work it out and tell you.
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
August 4, 2011 at 6:13 am
How would you like the below scenario to work
Insert a row with id 1
Insert a new row with select max( id ) +1 hence the new id = 2
Delete the latest row id = 2 deleted
Insert a new row using logic in step 2 hence new id = 2
result would look like 1,2
vs using identity
where the result would look like 1, 3
Since its a primary key I guess there would be a clustered index as well. this would have an impact in terms of missing values in the primary key column.
August 4, 2011 at 6:39 am
Hi Guys,
Thanks for all your replies, at the moment there is nothing set in stone, we are just reviewing the different options available:
1. Set "Identity" to "Yes" for all PK columns and have a procedure/function which will work out the latest inserted value for the PK and use that when inserting rows into another table as the FK.
2. Don't set "Identity" at all and have a procedure/function to work out what the PK column value should be and also use that when inserting rows into another table as the FK.
3. Another option.
As Jayanth_Kurup suggests, using the "select (max <col>) +1" could cause issues if the values are deleted. Therefore I'm thinking option 1 is the best approach - leave Sql Server to work out the value to insert into the PK column and then have a procedure/function on top to work out what the FK value will be based on the latest value assigned to the PK column.
Many thanks,
Chris
www.sqlAssociates.co.uk
August 4, 2011 at 6:43 am
Chris Kitchen (8/4/2011)
Hi Guys,Thanks for all your replies, at the moment there is nothing set in stone, we are just reviewing the different options available:
1. Set "Identity" to "Yes" for all PK columns and have a procedure/function which will work out the latest inserted value for the PK and use that when inserting rows into another table as the FK.
2. Don't set "Identity" at all and have a procedure/function to work out what the PK column value should be and also use that when inserting rows into another table as the FK.
3. Another option.
As Jayanth_Kurup suggests, using the "select (max <col>) +1" could cause issues if the values are deleted. Therefore I'm thinking option 1 is the best approach - leave Sql Server to work out the value to insert into the PK column and then have a procedure/function on top to work out what the FK value will be based on the latest value assigned to the PK column.
Many thanks,
Chris
Take a look at the OUTPUT clause for TSQL INSERT statements. SCOPE_IDENTITY() will get you want you need for single row inserts, but as soon as you start doing multi-row, you'll need to use the OUTPUT clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2011 at 6:50 am
Chris Kitchen (8/4/2011)
1. Set "Identity" to "Yes" for all PK columns and have a procedure/function which will work out the latest inserted value for the PK and use that when inserting rows into another table as the FK.
You don't need a function/procedure to work that out, and trying to do anything manual to work out the automatically assigned values is risky (imagine if another insert occurred between your insert and the running of the function to get the last value)
Read up on Scope_Identity and the OUTPUT clause. SQL can return you the values, no need to try and figure them out yourself.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply