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.

Oh no, my data is gone, there is serious corruption, run for the hills, abandon ship! Where is my passport! WHY DID we do this upgrade???? Why did we release this patch? Why is the Database guy so relaxed?

Probably because he/ she haven’t refreshed the views yet. What are these views you speak about?

A view is a dynamic “view” of data in tables. The issue with them are that for practical reasons and sometimes just because of CRAZY AND TOTALLY UNREALISTIC DEADLINES, we use this little thing: * an asterisk, and just like his Gaulish friend Asterix, is a small thing that packs a big punch, but like magic potion it should be used wisely, not wildly.

So, what happens, due to a structural change in the database, new fields get added to tables being referenced by the views. The views have its own list definition of the fields it should contain, and nobody told it to update. As an example a table had 10 columns and now it has 12.

Someone wrote a view that looked something like this :

Create view v_someview as select * from TenColumnTable t inner join SecondTable s on t.foreignkey = s.primarykey

Column 11 and 12 before the upgrade referenced the 1st 2 columns of “basetable”. After the upgrade when you go select * from v_ someview you see the name of column 1 of Secondtable in column 11 of the view, but the data of column 11 of TenColumnTable. How is this possible? Well when a view gets created it saves the underlying structure in the system tables just as a column, and a table function, and that needs to be refreshed for the data to display correctly.

exec sp_refreshview ‘v_ someview’ will do just that and all the madness will end.

So remember, if you ever make changes to a database’s structure, and all hell breaks loose, before you skip the country, refresh the views.

Sometimes you are doing SQL server maintenance, rebuilding huge indexes, or massive backups and you don’t know how far it is or what its doing.  We complied all of the scripts we have used in the past into a small simple easy to use tool so you can try and figure out if its better to wait for it at 3 in the morning, or kill it and wait for the rollback… and on the topic of killing, there’s a button…

Download it here.