Identity columns, we create them, and then forget about them, and for 12 years it has never been a problem. This is not an opinion about implicit and explicit Primary keys, and identity columns has been the easiest fastest best way to add a unique column to a database for decades. It has been used as primary keys on millions of Databases across the globe. And why not,  I have always been of the opinion that if the President cannot read it, then I don’t need to worry about it, so let it grow. They are everywhere, and most of the time you can forget about it but on Friday, the 29th of September 2017 the unthinkable happened. A table on a Database I have spent a lot of time with, suddenly could not insert values. The Identity value was out of range for an int.

Integers as with any other datatype are saved in Bytes. We live in a binary age and to make things more efficient at storing and retrieving, we need to define in which bytes we are going to save it . According to Microsoft the maximum value for an Integer is 2 147 483 647. This Gupta’esce number is big,  but a Bigint gets ridiculous at : 9 223 372 036 854 775 807.  After this event the question that lingers in my mind like a frustrated toddler on his/her way to a holiday is simple: Are we there yet?

This tries to answer that:

DECLARE @VLS VARCHAR(MAX) = ”
SELECT @VLS = @VLS + CASE WHEN ROW_NUMBER() OVER (ORDER BY t.name) = 1 THEN ”
ELSE ‘ UNION ALL ‘
END + ‘Select ”’ + t.name + ”’ as TableName,”’ + c.name + ”’ as ColumnName,”’ + tp.name + ”’ as ColumnType , CAST(max([‘ + c.name
+ ‘]) as bigint) as MAXValue,(1.00-(CAST(MAX([‘ + c.name + ‘]) AS BIGINT) /’
+ CAST(CASE WHEN tp.name = ‘bigint’ THEN CAST(9223372036854775807 AS BIGINT)
ELSE CAST(2147483648 AS BIGINT)
END AS VARCHAR(40)) + ‘))*100.00 as GrowthSpace

 

from [‘ + t.name + ‘] WITH (NOLOCK)’
FROM sys.columns c
INNER JOIN sys.types tp ON tp.user_type_id = c.user_type_id
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.is_identity = 1
ORDER BY t.name

 

 

EXEC (@VLS)

 

 

PRINT @VLS

 

Please don’t run it on a production environment during high load, but it should tell you how much room you have for growth.