DBCC CheckIdent

Being day six of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKIDENT.

Description:

DBCC CHECKIDENT is used for check on the current value in the identity column for a table. It also reports on the largest value in that column. DBCC CheckIdent can also be used to update or set the next identity value on a table.

If you call DBCC CheckIdent with just a table name, it will report back information, and it will also update the next identity value if there is a value in the table larger than the current next identity value. This can be dangerous if you are not expecting this. Use the NORESEED parameter to tell DBCC CheckIdent to not make any changes. You can also use the RESEED parameter with a specific number to set the identity value to a that number.

DBCC CHECKIDENT Syntax:

dbcc checkident
(
    'table_name'
    [ , { NORESEED
        | { RESEED [ , new_reseed_value ] }
    } ]
)
    [ WITH NO_INFOMSGS ]

Example:

The following example….

First we create a table called TestSmallIntIdentity with an id of type SMALLINT and a log time. Keep in mind that a SMALLINT accommodates values from -32768 to 32767. The default on the identity is to default to zero. If you are not familiar with this, take a look at my Are you wasting half the capacity of IDENTITY article.

Notice the table is created with the primary key being the id column to prevent duplicates.


CREATE TABLE [dbo].[TestSmallIntIdentity](
 [id] [smallint] IDENTITY(1,1) NOT NULL,
 [logTime] [datetime] NOT NULL DEFAULT GetDate(),
 CONSTRAINT [PK_TestSmallIntIdentity] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];

Once the table has been created we insert a single row, and look at the contents of the table, and the number of rows.

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT * FROM TestSmallIntIdentity;
SELECT count(*) FROM TestSmallIntIdentity;

At this point we have 1 row in the table. Now lets just add 1000 more rows into the table. This may take a minute to run depending on your server speed. Once complete, the SELECT * should return 1001 rows, and the count(*) should show 1001.

SET NOCOUNT ON;
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
GO 1000

SELECT * FROM TestSmallIntIdentity ORDER BY id DESC;
SELECT count(*) FROM TestSmallIntIdentity;

In the above example, we can see that the maximum id value in the table is 1001, and therefore the next identity value would be 1002.

Now we delete everything from the table, insert a single row, take a look at the id of the row, and run DBCC CheckIdent to see what we can about the identity.

DELETE FROM TestSmallIntIdentity;

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT * FROM TestSmallIntIdentity;
SELECT count(*) FROM TestSmallIntIdentity;

DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

As we speculated above the identity value that was inserted is 1002.

Next we delete everything from the table and check the identity, notice that the current identity value is 1002, and the current column value is ‘NULL’ implying in this case that there are no rows in the table. Since the table is empty it should be safe at this point to set the identity value to anything we want it to be (we will get to that in a minute).

DELETE FROM TestSmallIntIdentity;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Next we insert one thousand more rows, and check the identity value, and it will show 2002 as the current value.

SET NOCOUNT ON;
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
GO 1000

DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

So what happens if we delete part of the table. We know that the rows in the table now have id values of 1002 to 2002, so lets delete those that are greater than 1500. From here we check the identity, and see that the identity value is 2002, and the maximum column value is 1500.

DELETE FROM TestSmallIntIdentity WHERE id > 1500;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Knowing that the maximum value is 1500, if we are using trusted foreign keys, and can confirm that there are no other references to values in this table that have been deleted, we can just adjust the identity value to be 1501 as shown below.

DBCC CHECKIDENT (TestSmallIntIdentity, RESEED, 1501);

Check the identity value with DBCC CheckIdent, and we see (shown below) that the current identity value is set to 1501, and the max value in the column is 1500.

DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Next we insert a row into the TestSmallIntIdentity table, and check the the identity after, showing 1502 as the current value in the table, and the current identity value.

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Now what happens when the identity is full, meaning it has reached its maximum value for the data type used. We will try this by inserting almost enough rows to ready the 32,767 maximum of a SMALLINT data type.

DELETE FROM TestSmallIntIdentity;
DBCC CHECKIDENT (TestSmallIntIdentity, RESEED, 0);
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);
GO
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
GO 32765 -- Run it 32,765 times

Next we insert one row, and check to see where we are at with the identity. Everything looks good at this point, we can insert one more row.

DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

So we insert another with no errors, and check our current value, which shows as 32767, but we know the max value in a SMALLINT is 32767, so it must be full.

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

What happens when we insert one more as follows.

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

The Arithmetic overflow error message is shown:
Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.

What do we do now?

Now we can do one of the following:

Stop inserting rows into this table.
Change the size of the identity column and everywhere else that is foreign keyed to this ID to be something larger than a SMALLINT.
Just reset the identity to be the most negative number possible, to buy us some time.

Lets take a look at resetting the identity to be a negative number. Keep in mind that this only buys us some time, if we filled up the positive numbers is a SMALLINT, we will eventually use up the negative numbers. Warning, you may run into problems later once this increases to 0, and there are no more numbers left in the SMALLINT range.

Here is how to reset the identity to a new value, in this case -32768, or the most negative number that will fit into a SMALLINT.


DBCC CHECKIDENT (TestSmallIntIdentity, RESEED, -32768);

Notice in the above output, when we set the value to -32,768, there was not output that showed we had set it to -32,768. So let’s check to see what is is, using DBCC CheckIdent. Be sure to use the NORESEED parameter on CheckIdent. The NORESEED indicates that the check does not adjust the seed value for the identity. If this is run in this specific case without the NORESEED parameter, it will automatically adjust the identity value since it is less than the column value. It would set it to 32767, and we would end up in the same place as we were before we tried setting it to -32768.


INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Now we will use up most of the negative identity values from the SMALLINT, to get to the point that the identity value is -2. Here we know that all of the numbers that can fit into the SMALLINT that are less than or equal to -2 have been used, and we know that all the values greater than 0 have been used. the only numbers left are -1, and 0.

GO
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
GO 32765 -- Run it 32,765 times

-- now check to see where we are at.
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Next one more insert.


-- insert another and check
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Now we insert one more row, which uses up 0, and there no more numbers left after this insert.


-- and another
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Now we try one more insert anyway, and find out that we are violating the PRIMARY KEY constraint with duplicate keys.


-- and another
INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_TestSmallIntIdentity'. Cannot insert duplicate key in object 'dbo.TestSmallIntIdentity'. The duplicate key value is (1).
The statement has been terminated.

That’s it, we are now out of numbers for this primary key ID value with a SMALLINT data type. The next thing to try would be a larger data type like INTEGER.
Now lets check the identity value, and see how it looks. If we just run DBCC CheckIdent wihtout the NORESEED value it will reseed the value to 32767 without telling us. So lets run it a second time to check as shown here.


DBCC CHECKIDENT (TestSmallIntIdentity);
-- now check, and see that it has already changed.
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Now we try the final insert, this time it is trying to insert 32767 + 1 which as shown below causes an arithmetic overflow.

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
DBCC CHECKIDENT (TestSmallIntIdentity, NORESEED);

Summary

You can use DBCC CheckIdent to see what the current identity value is. You can also use it to optionally set the identity value to something you specify, or end up having it automatically set for you.

DBCC CHECKCONSTRAINTS – Check integrity of table constraints.

Are you wasting half the capacity of IDENTITY?

Notes:

For more information see TSQL Wiki DBCC checkident.

DBCC Command month at SteveStedman.com is almost as much fun as calculating identity values.