Identity Column Usage? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Identity Column Usage?

I have a table that I#%92m using an identity column in. This column (ItemID) is the second column of the key (with the first being "JobID"). I#%92d like to have the Identity field start from 1 each time a new JobID is entered. For instance.. JobID Item ID
00005313 1
00005313 2
00005313 3 00005399 1
00005313 2 Currently the Item ID field continues to increment without any regard for the JobID: JobID Item ID
00005313 1
00005313 2
00005313 3 00005399 4
00005313 5 Is what I’m trying to accomplish possible? Thanks for any help!
Well, if you are looking for MSSQL Server built-in feature supporting this behaviour then the answer is no.
I think you need to write your own function to create the values for the ItemId column. If you have speacil requirements for the values of this column (like restarting the number when I job id changes then you need a specialty routine. Seehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03k1.asp for some ideas on this.
A quick answer using a UDF that could most probably be optimized:<br /><br /><b><br />CREATE FUNCTION NewItemID (@JobID char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)<br />RETURNS smallint<br />AS<br />BEGIN<br />DECLARE @PrevJobID Char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@PrevItemID smallint<br />DECLARE @CurItemID smallint<br /><br />SELECT TOP 1 @PrevJobID=JobID,@PrevItemID=ItemID <br />FROM YourTable <br />ORDER BY JobID DESC<br /><br />IF @PrevJobID&lt;&gt;@JobID <br />SET @CurItemID= 1<br />ELSE<br />SET @[email protected] + 1<br />RETURN @CurItemID<br />END<br /><br /></b><br /><br />Use the function when inserting new values into your table thus:<br /><br /><b><br />INSERT INTO YourTable(JobID,ItemID) VALUES (‘00005444’,dbo.NewItemID(‘00005444’))<br /></b><br /><br />OR if you are using a stored proc with parameters:<br /><br /><b><br />INSERT INTO YourTable(JobID,ItemID) VALUES (@JobID,dbo.NewItemID(@JobID))<br /></b><br /><br /><br /><br /><br /><br /><br />Nathan H.O.<br />Moderator<br />SQL-Server-Performance.com
quote:INSERT INTO YourTable(JobID,ItemID) VALUES (@JobID,dbo.NewItemID(@JobID))

This solution might not work if two rows are being inserted in parallel for the same JobID. With right isolation level it may work, I am not quite sure about that. Also, in case you want to execute: insert into yourTable(JobID, ItemID)
Select JobID, dbo.NewItemID(JobID)
from query this is going to be slow because that way you actually execute correlated query. If these two potential drawbacks are irrelevant to you, then you have solution you were looking for.
Thank you all for your help! The solution posted by vbkenya will work for my application. I don’t anticipate running into the scenarios listed by mmarovic, but thanks for making me aware of them.
For performance: make sure that the JobID field has a clustered index on it Nathan H.O.
Moderator
SQL-Server-Performance.com
]]>