Is Identity

Transact-SQL
1

Hello Guys,

What is the best option to insert new record into a table where the primary key column is not incremental, I mean that the set identity is set to no. I have no permission to set it to yes before the insert and set it back to no.

Should I select the biggest ID and use ID+1 in the insert? There is better way?

Thanks!

2

If it's not an identity, the best way is to use a sequence object.

Sequence Object

3

You would definitely not want to set the identity property and then undo it for purposes of inserting data. Undoing the identity property involves dropping and recreating the table.

One way to insert the next ID is what you indicated, namely using the maximum existing ID. You have to be careful to avoid collisions if there are multiple clients inserting data into the table.

If you are on SQL 2012 or later, SEQUENCE numbers is another option. See here and here

4

Unfortunatelly we are using SQL2008R2.

So it seams thet my only option is go for max+1.

5

Is it help if I use 'with lock' ?

6

just wrap the select max() and the insert into in a transaction, isolation level repeatable read or serializable

7

The thing to keep in mind is that doing the MAX in an elevated isolation level as @gbritton suggested would effectively lock the table. So you want to minimize the time the lock is held. One thing you might consider is doing the update and select in a single statement, for example like this:

INSERT INTO YourTable
	(col1, col2, idCol)
SELECT
	@col1, @col2, MAX(idCol) + 1 -- won't work if there are no rows in the table.
FROM
	YourTable;

I have no facts to prove what I am going to say next, it is just based on my mental image of how the selects and inserts will work, so if I am wrong, I stand corrected:

If you have two spids selecting the max value and updating in separate statements, there is the possibility that it can end up in a deadlock. In that regard, I even think it might be better to take an exclusive lock on the table rather than serializable.

8

Thanks James! Good idea and keeps locking down. You could even handle the empty set case I think:

INSERT INTO YourTable
	(col1, col2, idCol)
SELECT
	@col1, @col2, MAX(_newId) + 1
FROM
(
        SELECT MAX(IdCol) FROM Yourtable
        UNION ALL
        SELECT 0
) _(_NewId))
9

Thank you guys!

10

I favour a separate sub-select for the MAX(IdCol) as it is easy to re-use that code if the INSERT becomes a multi-row insert (e.g. inserting from a #TEMP table). Can then include an IDENTITY column in the #TEMP table, or use a ROW_NUMBER() OVER() construction to get ascending numbers for each record to be inserted and then do something like

INSERT INTO YourTable
	(col1, col2, idCol)
SELECT
	T.col1, T.col2, N.IdCol_MAX + T.RowNumber
FROM
(
        SELECT MAX(IdCol) AS [IdCol_MAX]
        FROM Yourtable
        UNION ALL
        SELECT 0
        WHERE NOT EXISTS (SELECT * FROM Yourtable)
) AS N
    CROSS JOIN #TempTable AS T
11

Might need to add

WHERE NOT EXISTS (SELECT * FROM Yourtable)

? or an TOP 1 ... ORDER BY _NewID DESC

12

Redundant because of the

MAX(_newId) + 1

in the outer select

1 Like
13

The following will stop race conditions but is not very good for concurrency.

  1. Inserting single row:
WITH MaxID
AS
(
	SELECT COALESCE(MAX(ID), 0) AS ID
	FROM YourTable WITH (UPDLOCK, SERIALIZABLE)
)
INSERT INTO YourTable(ID, <YourCols>)
SELECT M.ID + 1, <YourCols>
FROM MaxID M;
  1. Inserting multiple rows from temp table etc
WITH MaxID
AS
(
	SELECT COALESCE(MAX(ID), 0) AS ID
	FROM YourTable WITH (UPDLOCK, SERIALIZABLE)
)
,RowOffSets
AS
(
	SELECT *
		,ROW_NUMBER() OVER (ORDER BY <YourCol(s)>) AS RowOffSet
	FROM #temp
)
INSERT INTO YourTable(ID, <YourCols>)
SELECT M.ID + R.RowOffSet, <YourCols>
FROM RowOffSets R
	CROSS JOIN MaxID M;

If only you insert into the table, and you only ever insert one row at a time a sequence type SP can be created:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE dbo.YourSeq
(
	Seq int IDENTITY(<CurrentMaxValueInYourTable>, 1) NOT NULL;
);
GO
CREATE PROCEDURE dbo.NextYourSeq
	@NextSeq int OUTPUT
AS
SET NOCOUNT ON;
BEGIN TRAN;
SAVE TRAN Seq;
INSERT INTO dbo.YourSeq DEFAULT VALUES;
SET @NextSeq = SCOPE_IDENTITY();
ROLLBACK TRAN Seq;
COMMIT;
GO

This can then be used like:

DECLARE @NextId int;
EXEC dbo.NextYourSeq @NextId OUTPUT;
INSERT INTO YourTable(ID, <YourCols>)
SELECT @NextId, <YourCols>;