CODEWITHSUNDEEP
sql-servert-sql
VJAI
VJAI

Reputation: 111

Listing out the missing Ids from an auto incrementing primarykey

I've a table Users which has a primary key user_id which is auto incremented when we insert a user_details.Now I've a requirement where in I need to list out all the missing(or deleted) user_ids. I've tried it this way.

DECLARE @NUMBER BIGINT = (SELECT MIN(USER_ID) FROM USERS)

DECLARE @MAX BIGINT = (SELECT MAX(USER_ID) FROM USERS)

CREATE TABLE TEMP_USERS (USER_ID BIGINT)

WHILE @NUMBER <= @MAX
BEGIN
    INSERT INTO TEMP_USERS
        VALUES(@NUMBER);

    SET @NUMBER = @NUMBER+1;
END

SELECT USER_ID from TEMP_USERS 
EXCEPT 
SELECT USER_ID from USERS

But It's taking too long. Is there an alternate way to increase the performance?

Thanks, Vijay

Upvotes: 0

Views: 57

Answers (1)

Arion
Arion

Reputation: 31249

If you are using MSSQL 2005+. Then you can do this:

DECLARE @NUMBER  BIGINT =(SELECT MIN(USER_ID) from USERS)
DECLARE @MAX BIGINT =(SELECT MAX(USER_ID) from USERS)

;WITH Nbrs ( n ) AS (
        SELECT @NUMBER UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < @MAX)
SELECT
    Nbrs.n AS USERID
FROM
    Nbrs
WHERE NOT EXISTS
(
    SELECT NULL FROM USERS
    WHERE USERS.USER_ID=Nbrs.n
)
OPTION ( MAXRECURSION 0 )

Update

To address the comment. What does MAXRECURSION do in this case. When we are using a recursive CTE function there is a possiblility that you write it in a way that it never ends. The servers MAXRECURSION is 100. This can be overridden by using a hint that it should not take the servers MAXRECURSION.

When we use 0 then it do not have any MAXRECURSION. If we would not set MAXRECURSION then after 100 rows it will crash and throw and exception that the MAXRECURSION has been meet.

Because we can expect that the OP has more the 100 missing user id in the sequence we need to indicate that we want to keep doing the recursion until n < @MAX

Upvotes: 2

Related Questions