Identify and list all missing from range of 1 to n

We have a table in that that store the ID in sequence but somehow in mid of the sequence few random rows are delete. We need to find that which number is deleted or which number is missing.

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE dbo.Products_demo (     product_id INT PRIMARY KEY,     product_name VARCHAR(50) );  INSERT INTO dbo.Products_demo VALUES (1,'Laptop'), (2,'Mouse'), (3,'Keyboard'), (5,'Monitor'), (6,'Printer'), (9,'Scanner'), (10,'Camera');

See the data in the table.

If we see here then we can find out 4,7 and 8 re missing in this sequence. Since this is very small table, we can find by looking this value but if it is large then not possible to find it. For that we need to use the below script.

declare @maxid int; set @maxid= (select max(product_ID) from Products_demo) select value from generate_series(1,@maxid) gs left join Products_demo pd on pd.product_id=gs.value where pd.product_id is null