The sp_CheckKeyValues routine returns a result set containing the integer value ranges represented in a selected table and column. The result set also includes the size of each range and the existing row count within each range.
The sp_CheckKeyValues stored procedure is useful for finding gaps in IDENTITY values. Do gaps affect the operation of surrogate keys? No! However, the information can be interesting. This routine is not just limited to checking IDENTITY values. It can be used to look for gaps in the values of any column with an integer data type. It does not assume that all values in the column are unique.
CREATE PROCEDURE dbo.sp_CheckKeyValues
@DBFetch varchar(4000),
@DBField varchar(100),
@DBUltra bit = 0
The first parameter (@DBFetch) specifies the source of rows to examine. The parameter value can be a table name (including a temporary table), view name, user-defined function call or SELECT statement.
The second parameter (@DBField) specifies the column to examine within the set of rows defined by @DBFetch.
The third parameter (@DBUltra) is optional and it determines whether the output will represent ranges of existing values or ranges of missing values. A value of zero (0) returns existing values and a value of one (1) returns missing values.
This example examines the primary key column of the Product table in my example database.
EXECUTE sp_CheckKeyValues 'Product','ProductID'
These examples examine the IDENTITY values in the ExampleID column of the extra table in my example database.
EXECUTE sp_CheckKeyValues 'Example','ExampleID',0
EXECUTE sp_CheckKeyValues 'Example','ExampleID',1
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
|
|
