======   ======

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.**
===== Concept =====

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:

  - Create a map table (containing TABLENAME, OLDID, NEWID) for Tables to be compressed
  - For each table to Compress:
      * Load the map table
      * Compute the NewIDs
  - ReMap each field in the database

===== SQL =====

<code>
--------------------------------------------------------------------------------------
-- 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.!
--
--------------------------------------------------------------------------------------
</code>