Auto-increment [id] field in a table in an SQL-Server database

Question

I have different tables, containing an id field. That field is typically defined as id (PK, int, not null).

Normally, when adding an entry to a table, I add the value of the id field myself, but I'm wondering if there's no automatic way to do that:

The following is not working: (there already is an entry with id being 1)

INSERT [dbo].[Settings] ([GroupId], [Value],    [Name], [Id],  [Description], [Admin])
       VALUES           (        1,    N'1', N'BLABLA',    1, N'meer blabla',       0)

The following is also not working (the id field not being filled in):

INSERT [dbo].[Settings] ([GroupId], [Value],    [Name], [Description], [Admin]) 
       VALUES           (        1,    N'1', N'BLABLA', N'meer blabla',      0)

I know about the possibility to add a kind of IDENTITY table, which might handle this, but I'm very reluctant to add another table to the customer's database.

Does anybody know a way to get this done? If possible, I would like to have two possibilities:

• Finding the first possible id (using holes in the existing ids, if any): imagine that I have the following list of existing ids: (1, 2, 3, 4, 11, 12, 13, 14). Then I want 5 to be the next one.
• Finding the highest id plus one: imagine that I have the following list of existing ids: (1, 2, 3, 4, 11, 12, 13, 14). Then I want 15 to be the next one.

Does anybody know how to get this done in a one-liner SQL command, without altering the customer's database?

For your information, I'm using Telerik OpenAccess for creating and accessing the database, and one of the items I can see is this:

configuration.HasProperty(o => o.Id).IsIdentity();

However, for that table, I don't see that in SQL-Server:

Thanks in advance

Answer 1

Typically the best way to get this done if you have to preserve the values in the id column and not manually insert the id is -

CREATE TABLE dbo.Settings_x
(
    GroupId INT               --or whatever datatype
  , value NVARCHAR(256)       --or whatever datatype
  , Name NVARCHAR(256)        --or whatever datatype
  , Id INT NOT NULL IDENTITY(1, 1)
  , Description NVARCHAR(256) --or whatever datatype
  , Admin BIT                 --or whatever datatype
  , CONSTRAINT PK_Settings_x
        PRIMARY KEY CLUSTERED (Id)
);

SET IDENTITY_INSERT dbo.Settings_x ON;
INSERT INTO dbo.Settings_x
(
    GroupId
  , value
  , Name
  , Id
  , Description
  , Admin
)
SELECT GroupId
     , value
     , Name
     , Id
     , Description
     , Admin
FROM dbo.Settings;
SET IDENTITY_INSERT dbo.Settings_x OFF;

EXEC sys.sp_rename @objname = N'dbo.Settings'  -- nvarchar(1035)
                 , @newname = N'Settings_old'; -- sysname
EXEC sys.sp_rename @objname = N'dbo.Settings_x' -- nvarchar(1035)
                 , @newname = N'dbo.Settings';  -- sysname
EXEC sys.sp_rename @objname = N'dbo.<youroldpkname>'   -- nvarchar(1035)
                 , @newname = N'<youroldpkname>_old'; -- sysname
EXEC sys.sp_rename @objname = N'dbo.PK_Settings_x'   -- nvarchar(1035)
                 , @newname = N'PK_Settings'; -- sysname

That will create a new table with the identity property of true on the id column, insert all of the current records with their existing id value, and then rename the current table and pk name to Settings_old and rename the new table and pk name to Settings.

With that, you could just run the second insert statement you tried running and be fine.

INSERT dbo.Settings
(
    GroupId
  , Value
  , Name
  , Description
  , Admin
)
VALUES
(1, N'1', N'BLABLA', N'meer blabla', 0)

If you wanted to just dynamically find the lowest available missing id in your id column, you could run this -

SELECT TOP 1
       gaps.prevID + 1 AS id
FROM
(
    SELECT id
         , LAG(id, 1, 0) OVER (ORDER BY id) AS prevID
    FROM dbo.Settings
) gaps
WHERE gaps.id - gaps.prevID > 1

and if you wanted to use that as part of your first insert statement, you could run this -

INSERT dbo.Settings
(
    GroupId
  , Value
  , Name
  , Id
  , Description
  , Admin
)
SELECT _row.GroupId
     , _row.Value
     , _row.Name
     , new_id.id
     , _row.Description
     , _row.Admin
FROM
(
    VALUES
        (1, N'1', N'BLABLA', N'meer blabla', 0)
) _row (GroupId, Value, Name, Description, Admin)
    CROSS APPLY
(
    SELECT TOP 1
           ids.id
    FROM
    (
        SELECT gaps.prevID + 1 AS id
        FROM
        (
            SELECT Id
                 , LAG(Id, 1, 0) OVER (ORDER BY Id) AS prevID
            FROM dbo.Settings
        ) gaps
        WHERE gaps.Id - gaps.prevID > 1
        UNION ALL
        SELECT MAX(Id) + 1 AS id
        FROM dbo.Settings
    ) ids
    ORDER BY id ASC
) AS new_id;

That would dynamically find the lowest unused id value and use it in your insert statement. If there were no gaps in your table, it would return the next id you could use.

Hope that helps. Feel free to ask any followup.