Michael J. Swart

My friend pointed out an interesting RCA by Github where a

database table’s auto-incrementing ID column exceeded [maxint]. When we attempted to insert larger integers into the column, the database rejected the value

This led to a discussion about setting up monitoring for this kind of problem in our software. We have a place for monitoring and health-checks for all our databases. We just need to know how to define them.

So how do I create a script that reports any tables whose current identity values are above a certain threshold? This is what I came up with. Maybe you’ll find it useful too.

Find Tables in SQL Server Running Out Of Identity Room

declare @percentThreshold int = 70;
 
select t.name as [table],
       c.name as [column],
       ty.name as [type],
       IDENT_CURRENT(t.name) as [identity],
       100 * IDENT_CURRENT(t.name) / 2147483647 as [percent full]
from   sys.tables t
join   sys.columns c
       on c.object_id = t.object_id
join   sys.types ty
       on ty.system_type_id = c.system_type_id
where  c.is_identity = 1
and    ty.name = 'int'
and    100 * IDENT_CURRENT(t.name) / 2147483647 > @percentThreshold
order  by t.name

Other Notes

• I really prefer sequences for this kind of thing. Monitoring goes along similar lines
• I only worry about ints. Bigints are just too big.

I was looking at the docs for DROP TABLE and I noticed this in the syntax: [ ,...n ]. I never realized that you can drop more than one table in a statement.

You Still Have to Care About Order

I think that’s great. When dropping tables one at a time. You always had to be careful about order when foreign keys were involved. Alas, you still have to care about order. The docs say:

If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

That means that when you run

CREATE TABLE A ( id INT PRIMARY KEY );
CREATE TABLE B ( id INT FOREIGN KEY REFERENCES A(id) );
 
DROP TABLE IF EXISTS A, B;

It fails with

Msg 3726, Level 16, State 1, Line 4
Could not drop object ‘A’ because it is referenced by a FOREIGN KEY constraint.

But this ordering

DROP TABLE IF EXISTS B, A;

succeeds.

I think that order shouldn’t matter here. It’s not very SQL-like. If you think so too, vote for this suggestion In DROP TABLE statement, make table order irrelevant.

DROP TABLE IF EXISTS fails

This is a suggestion made by Matt Smith. Currently DROP TABLE behaves this way:

• DROP TABLE IF EXISTS succeeds if the table exists and is deleted.
• DROP TABLE IF EXISTS succeeds when there is no object with that name.
• DROP TABLE IF EXISTS fails when that object name refers to an object that is not a table

For example, this script

CREATE VIEW C AS SELECT 1 AS One;
go
DROP TABLE IF EXISTS C;

gives the error

Msg 3705, Level 16, State 1, Line 9
Cannot use DROP TABLE with ‘C’ because ‘C’ is a view. Use DROP VIEW.

It’s really not in the spirit of what was intended with “IF EXISTS”. If you want to vote for that suggestion, it’s here DROP TABLE IF EXISTS fails

DROP TABLE is not Atomic

I’ve gotten really used to relying on atomic transactions. I know that when I update a set of rows, I can rely on the fact that all of the rows are updated, or in the case of an error, none of the rows are updated. There’s no situation where some of the rows are updated. But a DROP TABLE statement that tries to drop multiple tables using the [ ,...n ] syntax doesn’t behave that way. If there’s an error, SQL Server continues with the list dropping the tables that it can.

We can see that with the first example. Here it is again:

CREATE TABLE A ( id INT PRIMARY KEY );
CREATE TABLE B ( id INT FOREIGN KEY REFERENCES A(id) );
 
DROP TABLE IF EXISTS A, B;
-- Could not drop object 'A' because it is referenced by a FOREIGN KEY constraint.
-- B is dropped

That example throws an error and drops a table.

The same nonatomic behavior is seen in a simpler example:

CREATE TABLE D ( id INT );
 
DROP TABLE E, D;
-- Invalid object name 'E'
-- D is dropped