Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Dec 06, 2012 05:12 AM by wmec
Dec 05, 2012 04:23 PM|LINK
I have a table with INT PrimaryKey Column. I do not want to use GUID or IDENTITY as Primary Key.I wanted to know what is the best possible way to get the next ID without using an IDENTITY or GUID as Primary Key column in the table. I do not
mind using GUID or IDENTITY in the table unless its the Primary Key column.
I have to find the next available ID (i.e get the MAX ID and increment it by 1),and use that:
Select @id=ISNULL(max(AlbumId)+1,1) from Albums
However, I want to prevent other applications from inserting into the table when I'm doing this so that we don't have any problems.
NOTE:I have gone through this[^]
answer and could not understand it properly. Would anyone be kind enough to explain me what is it they are doing?? And can i use the same technique??
Dec 05, 2012 05:27 PM|LINK
I believe you'd be able to use the following T-SQL to obtain the 'next id' -after- you start a Transaction.
SELECT ISNULL(max(AlbumId)+1,1) AS 'NextAlbumID'
WITH (TABLOCK, HOLDLOCK)
Then you can use the returned value. Just keep in mind two things:
In any case, i think i'd advise against trying to predict the database state. If you want to record nesting information (a header record in one table and some children records at another) then maybe you should consider sending both in one call, process it
db-side and let it figure out IDs and such.
Hope it helps!
Dec 06, 2012 05:12 AM|LINK
I still suggest you use an identity column for this, due to multi-user update to this PK column.