Reserve a range of values in a table in SQL Server

I want to reserve a range of values for the system like [1-10,000]. The user values should be inserted after 10,000. eg. There is a table that will have values inserted by the system & will also have values inserted by the user. So, when a system inserts, the id’s assigned to it will have to be between 1 to 10,000. If the user inserts a value, the values can be anything greater than 10,000.

Answer

You can use check constraint to ensure the condition has been satisfied:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [Col01] INT
   ,CONSTRAINT [CH_StackOverflow] CHECK ([Col01] < 1 OR [Col01] > 10000)
);


INSERT INTO [dbo].[StackOverflow] ([Col01])
VALUES (-1);

INSERT INTO [dbo].[StackOverflow] ([Col01])
VALUES (1);

If the column must be populated automatically, you can use IDENTITY column like this:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [Col01] INT IDENTITY(10001, 1)
   ,[Col02] NVARCHAR(12)
);


INSERT INTO [dbo].[StackOverflow] ([Col02])
VALUES ('x');

INSERT INTO [dbo].[StackOverflow] ([Col02])
VALUES ('y');

SELECT *
FROM [dbo].[StackOverflow];

Lately, you can use SET IDENTITY_INSERT to add your special records.

If you need more control, you can use trigger – instead of/after INSERT and UPDATE. You can add more logic there – reject the user input or transform the user input. But it feels like an overkill and you need to be careful when creating triggers – always process rows in batches to ensure you are not affecting to much your CRUD performance.