Accidental DBA Tip – Max Value of Identity Column
SQL Server databases love to have an incrementing column as a clustered index value. This may be the primary key of a table or not, depending on the need. However, a system assigned incrementing column is recommended for the clustered index.
Typically, the data in this type of column is generated using an IDENTITY function built into SQL Server. This allows the database to assign the next value. When the IDENTITY is configured it can be set to a starting value, and an increment for each new row. The default is to start at 1 and increment by 1 for each row. Nothing says you can’t increment by a different value, or that the incremental value has to be a positive number.
You can create identity columns on integer based data types in SQL Server. Once your Identity value reaches the max allowed for that data type you must find a way to resolve the problem by either changing your schema to use a bigger data type, changing your current seed, or other options more complicated than those two. Here are the data types and the range of numbers allowed, taken from books online @ SQL Server Int DataTypes.
| Data Type | Min Value | Max Value |
| Bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| Int | -2,147,483,648 | 2,147,483,647 |
| Smallint | -32,768 | 32,767 |
| Tinyint | 0 | 255 |
You can proactively review your database and determine if the current contents is reaching the max value allowed.
Here is a simple query that will provide you a list of the databases, tables and columns to review. The query can be modified to also include the data type or even run a query to determine current valus. My intention isn’t to provide you with complete code, but to give you a start on your research.
SET NOCOUNT ON
CREATE TABLE #Tables (dbname SYSNAME, TableName SYSNAME, ColumnName SYSNAME)
EXEC msdb.dbo.sp_msforeachdb
'
DECLARE @CMD VARCHAR(MAX) = ''''
DECLARE @Table SYSNAME, @Column SYSNAME, @CONTINUE CHAR(1) = ''T''
DECLARE c CURSOR FOR
SELECT SO.Name, SC.Name
FROM [?].sys.identity_columns si WITH (NOLOCK)
JOIN [?].sys.syscolumns sc WITH (NOLOCK) on si.Object_id=sc.id
AND si.column_id = sc.colid
JOIN [?].sys.sysobjects so WITH (NOLOCK) on si.object_id = so.id
WHERE so.Type = ''u''
AND ''?'' NOT IN (''Master'', ''MSDB'', ''TempDB'',''Model'')
OPEN C
WHILE @Continue = ''T''
BEGIN
FETCH NEXT FROM C INTO @Table, @Column
IF @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Tables
SELECT ''?'', @Table, @Column
END
ELSE
BEGIN
SET @Continue = ''F''
END
END
CLOSE C
DEALLOCATE C
'
SELECT * FROM #Tables
If you get the min and max values of the identity columns for each of these tables, you can evaluate if the data is close to exceeding the acceptable limits of the data type of the identity column.
Perhaps others have a script taking this to the next level. Please leave your comment online, or drop an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Understanding SQL Server Full-text Indexing – (Part 1)
In this article, you will learn about full-text indexing, how to configure an SQL Server instance to support full-text search, and how to create full-text index using SQL Server Management Studio
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)
Featured Script
dba3_Beginners_InsertFromAnotherTable_Demo
http://bitonthewire.wpengine.com/forum/ShowPost.aspx?PostID=2432 get all the data from cases and insert into case_history + date and by w… (read more)