2

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

In some situations I need to work with primary keys before 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
Maxim Shevtsov
  • 212
  • 1
  • 3
  • 10
  • so, you want to insert only the primary key column first by incrimination and putting all the remaining column values to "Null" right? – Sai Kalyan Kumar Akshinthala Sep 28 '11 at 04:38
  • 1
    Next version of SQL Server will introduce `sequence` that I guess fits your requirement. Here is a blog post by Aaron Bertrand about that and other techniques that can be used today. https://sqlblog.org/2010/11/11/sql-server-v-next-denali-using-sequence – Mikael Eriksson Sep 28 '11 at 05:05
  • 1
    In brief: **you cannot** - period. The `IDENTITY` column value is only establish **once you insert the row** - that's the way it works, and that's the way you should use it. See the comment just above for new features that SQL Server "Denali" will bring (i.e. *SEQUENCE* as a full-blown DB object) – marc_s Sep 28 '11 at 05:31

2 Answers2

1

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?

datagod
  • 1,031
  • 1
  • 13
  • 21
  • 1
    +1 also: an IDENTITY column doesn't **have** to increment by 1 all the time....it's the default, but it could be different.... – marc_s Sep 28 '11 at 05:30
0

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()
Jarek Przygódzki
  • 4,284
  • 2
  • 31
  • 41