Manage Learn to apply best practices and optimize your operations.

Utility routine: sp_OrganizeKeyValues

This routine organizes the IDENTITY values into a single contiguous range (1-N) for a selected table.

The sp_OrganizeKeyValues routine organizes the IDENTITY values into a single contiguous range (1-N) for a selected table.

The sp_OrganizeKeyValues stored procedure is useful for eliminating gaps in the IDENTITY values in the primary key column of a table. Do gaps affect the operation of surrogate keys? No! However, some people prefer to have their data as orderly as possible. This routine rearranges rows within the indicated table to close gaps in the IDENTITY values. The result is a single contiguous range of IDENTITY values from 1 through N, where N is the number of rows in the table. It also resets the next available IDENTITY value.

The sp_OrganizeKeyValues stored procedure preserves the integrity of foreign keys that reference rows in the affected table. It automatically adjusts the foreign key values in all child tables of the affected table.

    CREATE PROCEDURE dbo.sp_OrganizeKeyValues
        @DBTable varchar(100),
        @DBUltra bit = 0

The first parameter (@DBTable) specifies the table to be affected. The table must follow the rules of the surrogate key architecture suggested in my earlier article.

The second parameter (@DBUltra) is optional and it determines whether the routine will minimize the movement of rows or preserve the relative order of rows based on their IDENTITY values. A value of zero (0) means row movement will be minimized and a value of one (1) means the relative order will be preserved.

This example reorganizes the IDENTITY values in the extra table in my example database. It minimizes row movement.

Note: Be sure to DROP and CREATE the extra tables using the SQL code above before trying this example. The actions performed by sp_OrganizeKeyValues can be observed by checking the output of the surrounding SELECT statements.

SELECT * FROM Example
SELECT * FROM ExampleDetail

EXECUTE sp_OrganizeKeyValues 'Example'

SELECT * FROM Example
SELECT * FROM ExampleDetail

This example reorganizes the IDENTITY values in the extra table in my example database. It preserves the relative order of the rows.

Note: Be sure to DROP and CREATE the extra tables using the SQL code above before trying this example. The actions performed by sp_OrganizeKeyValues can be observed by checking the output of the surrounding SELECT statements.

SELECT * FROM Example
SELECT * FROM ExampleDetail

EXECUTE sp_OrganizeKeyValues 'Example',1

SELECT * FROM Example
SELECT * FROM ExampleDetail



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
This was last published in March 2006

Dig Deeper on SQL Server Database Modeling and Design

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close