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?? Thanks.
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'
FROM Albums
WITH (TABLOCK, HOLDLOCK)
Then you can use the returned value. Just keep in mind two things:
I think queries that causes updates (UPDATE, INSERT INTO, DELETE) will be put on lock wait state until you finish your Transaction. Depending on how you update this structure, the delays can be really bothersome.
If you fail to finish the transaction for one reason or another, it will stay locked. So beware.
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!
Marked as answer by Chen Yu - MSFT on Dec 17, 2012 01:56 AM
bronxbull
Member
237 Points
255 Posts
How to get the next ID from DB Table without an IDENTITY or GUID Column
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:
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??
Thanks.
OnoSendai
Participant
1144 Points
239 Posts
Re: How to get the next ID from DB Table without an IDENTITY or GUID Column
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.
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!
wmec
Contributor
6224 Points
3221 Posts
Re: How to get the next ID from DB Table without an IDENTITY or GUID Column
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.
HuaMin Chen