Manage Learn to apply best practices and optimize your operations.

Utilities to maintain a surrogate key architecture

You've discovered why and when to work with a surrogate key architecture, and how to establish its framework. In the third installment of this series, learn how to maintain the database with utility routines and stored procedures.

In feature 1 of this series I described a surrogate key architecture and explained how and why to use it. I also provided several tools to support the proposed architecture. In feature 2, I expanded on this theme with a collection of standard table columns and table-specific objects to support the framework. I also provided several tools to generate and manage CRUD routines.

This article offers some additional commentary about the use of SQL Server IDENTITY values as surrogate keys. I will describe several utility routines to help maintain a surrogate key database. I will also provide six stored procedures to implement the utility functionality.

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

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

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.