This document describes the process (and SQLs) used to compress the IDs in a table (or many tables). For example, if you started with a table with 100 records that were scattered between ID=1001 and ID=5873, this routine could compress them so that the IDs ranged from 100 to 200.

WARNING: This article is for reference. The changes made will PERMANENTLY affect your data. This should only be carried out by a skilled implementer and SQL practitioner. Always Backup your data before running this routine.

The idea of this approach is to use SQL queries to renumber and insert the relevant data from one table to another. The basic approach for each table will be:

  1. Create a map table (containing TABLENAME, OLDID, NEWID) for Tables to be compressed
  2. For each table to Compress:
    • Load the map table
    • Compute the NewIDs
  3. ReMap each field in the database
--------------------------------------------------------------------------------------
-- Section: Declare Variables and Create Databases
--
-- USE Scott_CompressTest;
-- =================                              ===========
-- ================= Create the Stored Procedures ===========
-- =================                              ===========
go
        --------------------------------------------------------------------------------------
        -- Section: Create_IDMapTable
        --
        --------------------------------------------------------------------------------------
        CREATE PROCEDURE Create_IDMapTable
        AS
        BEGIN
            IF OBJECT_ID('IDMap') IS NOT NULL
              DROP TABLE IDMap
            ;
            CREATE TABLE IDMap
            (
               TableName varchar(50) NULL,
               OldID int NOT NULL,
               ClassTypeID int NOT NULL,
               NewID int NULL,
               RowIndex int IDENTITY(1,1) NOT NULL
            )
            ;
            CREATE INDEX IDMap_OldIDIndex ON IDMap (OldID ASC)
            ;
            CREATE INDEX IDMap_NewIDIndex ON IDMap (NewID ASC)
            ;
            CREATE INDEX IDMap_RowIndex ON IDMap (RowIndex ASC)
            ;
        END
go
        --------------------------------------------------------------------------------------
        -- Section: Fill_IDMap Stored Procedure
        --
        -- This section fills the IDMap with values that need to be remapped
        --------------------------------------------------------------------------------------
        CREATE PROCEDURE Fill_IDMap
                @TableName varchar(50),
                @CustomWhereClause varchar(max) = ''
        AS
        BEGIN
            DECLARE @S varchar(max);
            -- Insert the values into the new Table
            -- We have to copy the entire table, but use the old ID as the NewID when the CustomerWhereClause is false
            SET @S =
                        'INSERT INTO IDMap '+
                        'SELECT DISTINCT ('''+@TableName+''') as TableName, ID as OldID, '+
                        '  ClassTypeID, ';
            IF (Coalesce(@CustomWhereClause, '')  '')
                        SET @S = @S + '  CASE WHEN ('+@CustomWhereClause+') THEN NULL ELSE ID END as NewID '
            ELSE
                        SET @S = @S + '  NULL as NewID '
            SET @S = @S +
                        'FROM '+@TableName+' '+
                        'WHERE ID> 0 '
            Execute(@S);
            -- Update the Map with the ID of the Destination Table for the remaining NewIDs
            --
            UPDATE TheMap
            SET NewID = 100 + coalesce( MapSum.NewID , 0)
            FROM IDMap as TheMap
            LEFT JOIN
              (Select TableName, OldID, ( SELECT COUNT(1) FROM IDMap I2 WHERE I2.TableName = I1.TableName AND I2.OldID <I1.OldID ) as NewID
               FROM IDMap as I1 WHERE (TableName = @TableName) ) MapSum on MapSum.TableName = TheMap.TableName and MapSum.OldID = TheMap.OldID
            WHERE (TheMap.NewID IS NULL) and (TheMap.TableName = @TableName)
       END
go
        --------------------------------------------------------------------------------------
        -- Section: Remap_Field Stored Procedure
        --
        -- This section defines a stored procedure that remaps the values in a table based
        -- on the values stored in the IDMap table.
        --
        -- Other Notes:
        --   * An optional WhereClause can be specified for the table to control the replacement
        --------------------------------------------------------------------------------------
        CREATE PROCEDURE Remap_Field
                @TableName varchar(50),           -- = 'CustomerGoodsItem'
                @FieldName varchar(255),          -- = 'CategoryID'
                @LookupTableName varchar(50),     -- = 'PricingElement'
                @CustomWhereClause varchar(max) = ''
        AS
        BEGIN
            -- Update the Field
            DECLARE @S varchar(2048);
            SET @S =
        '            UPDATE TheTable
                     SET '+@FieldName+' = coalesce(IDMap.NewID, '+@FieldName+')
                     FROM '+@TableName+' AS TheTable
                     LEFT JOIN IDMap
                     ON TheTable.'+@FieldName+' = IDMap.OldID
                     WHERE IDMap.TableName = '''+@LookupTableName+'''
                        AND TheTable.'+@FieldName+' IS NOT NULL
        '
            IF (Coalesce(@CustomWhereClause, '')  '')
              SET @S = @S + ' AND ('+@CustomWhereClause+')'
            Execute(@S);
        END
        --------------------------------------------------------------------------------------
go
-- =================                      ===========
-- ================= Create the Map Table ===========
-- =================                      ===========
print 'Creating IDMap Table'
exec Create_IDMapTable;
-- =================                              ===========
-- ================= Build the Map for each Table ===========
-- =================                              ===========
print 'Executing Fill_IDMap'
-- exec Fill_IDMap '', @SourceDatabase, @DestDatabase, ''
exec Fill_IDMap 'AdvQuery'
exec Fill_IDMap 'RuleMacro'
exec Fill_IDMap 'RuleAction'
exec Fill_IDMap 'Journal'       , 'ID in (Select ID from RuleAction where ID> 0 union Select ChildItemID as ID from RuleAction where ID> 0  AND ChildItemID IS NOT NULL)'
exec Fill_IDMap 'ReportMenuItem', 'ID in (Select ChildItemID from RuleAction where ChildItemClassTypeID IN (17240,17250,17270,17290))'
exec Fill_IDMap 'EmailActivity'
-- =================                                ===========
-- ================= ReMap each Field in each Table ===========
-- =================                                ===========
print 'Executing Remap_Field'
-- exec Remap_Field '', '', '', ''
exec Remap_Field 'RuleMacro'      , 'ID' , 'RuleMacro'
exec Remap_Field 'AdvQuery'       , 'ID' , 'AdvQuery'
exec Remap_Field 'EmailActivity'  , 'ID' , 'Journal'
exec Remap_Field 'RuleAction'     , 'ID' , 'Journal'
exec Remap_Field 'Journal'        , 'ID' , 'Journal'
exec Remap_Field 'ReportMenuItem' , 'ID' , 'ReportMenuItem'
exec Remap_Field 'RuleMacro'      , 'QueryID' , 'AdvQuery'
exec Remap_Field 'RuleAction'     , 'ParentID', 'RuleMacro'
exec Remap_Field 'ReportMenuItem' , 'ParentID', 'Journal'     , 'TheTable.ParentClassTypeID = 23130'
-- -- The RuleAction.ChildItemID maps to the Journal when the ClassTypeID is not 23130
-- -- Otherwise, it maps to the ReportMenuItem table.
exec Remap_Field 'RuleAction', 'ChildItemID', 'Journal'       , 'TheTable.ChildItemClassTypeID IN (21150,21350,20061)'
exec Remap_Field 'RuleAction', 'ChildItemID', 'ReportMenuItem', 'TheTable.ChildItemClassTypeID  IN (17240,17250,17270,17290)'
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--
-- Congratulations!  You are done.!
--
--------------------------------------------------------------------------------------
You could leave a comment if you were logged in.