An interesting scenario, which prompted me to write the script being shared.
WHen defining numeric data types such as int
and adding the IDENTITY
property, you would define the start of the range and the increment value, as below:
CREATE TABLE dbo.SampleTable (
[ID] [int] IDENTITY (1,1) NOT NULL,
[Description] [varchar] (50) NOT NULL,
...
...
)
In the above example the “ID” values will start from 1 and increment by 1, which is what would seem logical.
This however means that you would only be using half of the range supported by the int
data type, or rather, you’d be wasting half of the possible values.
The int
data type allows for whole numbers ranging from -2,147,483,648 to 2,147,483,647, giving more than 4.2 billion possible values.
The scenario which prompted an investigation and this script was that the table/column ran out of integer values - watch out for a future article to explain the root cause.
Once the issue was fixed by changing the data type from int
to bigint
, hence increasing the range to 9.2 trillion positive values, we needed to identify if any other tables were approaching the upper limit. Which is what this script does by executing the IDENT_CURRENT
function for each table retrieved using the CTE.
The script can be downloaded from here: GetCurrent-IdentityValue.sql
To read more about SQL Server data types, see this: int, bigint, smallint, and tinyint (Transact-SQL)
More information about the IDENT_CURRENT
function is available here: IDENT_CURRENT (Transact-SQL)