Just want to run something past you all...
The other day we had an issue with an application that basically maxed out the 2.1 billion+ limit in just a few hours on a identity (int) column.
I was lucky enough that this table was a staging table can could be truncated. Problem solve and application fixed.
My question is: Do you monitor on a regular basis the current identity field for any given table. I so how do you achieve this. As when I ran a DBCC CHECKIDENT on this particual column it took over 5 minutes.
asked Jul 13 '10 at 01:19 PM in Default
IDENT_CURRENT is your friend :)
Edit -> Wow, that was really embarrasing. I wrote a proc a few years back to gather those stats, and it was huge. Now it's eminently simple. I've just written it here as a multi-statement function, but it could just as easily be an in-line function.
I really hope no-one saw this post before I edited it!!
Ok, here's another one (just a plain SELECT), that gives you the maximum value for integer and numeric / decimal columns. Note that it doesn't go all the way up to the limit on numeric and decimal columns, because POWER() epic fails when raising 10 to the power of 29 or more, and gets the wrong value on 10 ^ 28.