HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
incrementidentitytheproperlysqlhowserverisnintset

Problem

When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what is the best practices way of doing this.

The naive approach would be to query the data, increment it, and then use that for an insert.

For example:

INSERT INTO myTable
(
  ID,
  Data
)
Values
(
  (SELECT MAX(ID) FROM myTable) + 1,
  "My actual data."
)


But I'm unsure if this is actually the best approach. Namely, since this is all one statement, is there a risk for a heavily utilized system to have another row inserted between selecting the MAX(ID) and the insert.

Edit:
Sql Server version 2012.

Edit 2: To specify, I'm looking for a DML solution as I do not have DDL rights to modify the table.

Solution

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain.

Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest benefits here is that you can also specify a number of values to be cached for faster retrieval if you're working with rapid inserts.

CREATE SEQUENCE seq_obj
    START WITH 1050
    INCREMENT BY 1
    CACHE 1000
GO


Then to get the next ID value, use:

SELECT NEXT VALUE FOR seq_obj


Which will provide 1050, then 1051, and so on and so forth.

MS Docs for full information on other properties.

Code Snippets

CREATE SEQUENCE seq_obj
    START WITH 1050
    INCREMENT BY 1
    CACHE 1000
GO
SELECT NEXT VALUE FOR seq_obj

Context

StackExchange Database Administrators Q#89851, answer score: 4

Revisions (0)

No revisions yet.