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
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
|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