The sp_ResetNextKeyValue routine resets the next available IDENTITY value for selected tables.
The sp_ResetNextKeyValue stored procedure is useful for helping to maximize the potential range of IDENTITY values. If the rows most recently inserted into a table were subsequently deleted, this routine allows the IDENTITY values allocated for those rows to be reassigned. The next row inserted will be assigned an IDENTITY value that is one increment larger than the largest existing IDENTITY value. The increment for IDENTITY values is almost always one (1), but that's not a requirement.
CREATE PROCEDURE dbo.sp_ResetNextKeyValue
@DBIntra varchar(8000) = NULL,
@DBExtra varchar(8000) = NULL,
@PCIntra varchar(100) = NULL,
@PCExtra varchar(100) = NULL
The four parameters are all optional and they work together to form a combination of table selection criteria using names.
The first and second parameters (@DBIntra/@DBExtra) are lists of table names separated by pipes (vertical bars). The @DBIntra parameter specifies table names to be included. The @DBExtra parameter specifies table names to be excluded.
The third and fourth parameters (@PCIntra/@PCExtra) offer table selection based on pattern matching names. The @PCIntra parameter includes tables by using a LIKE operator on the names. The @PCExtra parameter excludes tables by using a NOT LIKE operator.
The effects of these four parameters are combined with AND operators. Often only one of the parameters (or none) would be used for a given call, but it may be useful to provide @DBExtra and/or @PCExtra in combination with @PCIntra in order to work with the desired subset of tables. If the parameters are omitted, or if null values are provided, they are effectively ignored for selection purposes.
The method used to select tables in these routines is not necessarily the most efficient way of handling delimited strings. The CHARINDEX function was used for simplicity because performance is not a significant issue when referencing tables with very modest row counts in an administrative routine. If this kind of selection task were being done in a production routine that referenced tables with larger row counts, it would probably be better to parse the delimited string and use the result set for a join.
This example resets the next available IDENTITY value for the extra table in my example database. The change can be observed by checking the output of the two surrounding sp_CheckRowCounts stored procedure calls.
EXECUTE sp_CheckRowCounts 'Example'
EXECUTE sp_ResetNextKeyValue 'Example'
EXECUTE sp_CheckRowCounts 'Example'
Utilities to maintain a surrogate key architecture
Home: Introduction
Part 1: What are surrogate key values
Part 2: T-SQL code to create stored procedures
Part 3: Utility routine: sp_CheckRowCounts
Part 4: Utility routine: sp_CheckKeyValues
Part 5: Utility routine: sp_ResetNextKeyValue
Part 6: Utility routine: sp_OrganizeKeyValues
Part 7: Utility routine: sp_FindDuplicateRows
Part 8: Utility routine: sp_ChangeParentValue
More advice:
Surrogate key architecture to perform powerful database operations
Framework to support a surrogate key architecture
| ABOUT THE AUTHOR: |
|
Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget
|
|
