开发者

How to increment the Identity column

I am using identity columns as a primary key in my tables.

In some situations I need to work with primary keys befo开发者_如何学运维re inserting a new row.

For example, in Oracle I use : select <sequence_name>.nextval into <variable> from dual and I was sure that no one will insert any row with the same ID while my sp was executing.

As for SQL Server I can read the current identity value and it's increment, but there is no way to increment it without inserting a row.

Updated: The question is - how can I accomplish my task to work with ID (as identity column) in SQL Server before inserting a row and be sure that it will be unique at the end of my stored procedure.

Updated:I have a table with HierarchyId column.The way to form the first level of hierarchy,in my case, is to insert the hierarchyId column, according to indentity column. That is how I'v done it now:

begin transaction

insert into [dbo].[Group](GroupTypeId,CompanyOwnerId,GroupHierarchyId)
     values(@GroupTypeId,@HeaderCompanyId,null)

     update [dbo].[Group] 
      set GroupHierarcyId=hierarchyid::GetRoot().GetDescendant(cast ('/'+cast(@NewGroupId as varchar)+'/' as hierarchyid),null) 
     where GroupId=scope_identity()

    commit


You can put an exclusive lock on the table, get the maximum ID, add 1 to it. That will be your next ID. Insert your data, the unlock the table.

HOWEVER,

I cannot fathom why you would want to work with a value before it is created. Can yo post a bit more information on that?


If you need a key that would be unique across databases and database servers, then the GUID's (Global Unique Identifier) certainly fulfills this need. If you want to generate a new GUID server the you can simply use the NEWID() function

SELECT NEWID()
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜