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

1. For each table to Compress:
   • Load the map table
   • Compute the NewIDs

1. ReMap each field in the database

code

– Section: Declare Variables and Create Databases

–

– USE Scott_CompressTest;

–

–

–

go

1. ————————————————————————————-

1. - Section: Create_IDMapTable

1. -

1. ————————————————————————————-

      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

1. ————————————————————————————-

1. - Section: Fill_IDMap Stored Procedure

1. -

1. - This section fills the IDMap with values that need to be remapped

1. ————————————————————————————-

      CREATE PROCEDURE Fill_IDMap

              @TableName varchar(50),

              @CustomWhereClause varchar(max) = ''

      AS

      BEGIN

          DECLARE @S varchar(max);

1. - Insert the values into the new Table

1. - 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);

1. - Update the Map with the ID of the Destination Table for the remaining NewIDs

1. -

          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

1. ————————————————————————————-

1. - Section: Remap_Field Stored Procedure

1. -

1. - This section defines a stored procedure that remaps the values in a table based

1. - on the values stored in the IDMap table.

1. -

1. - Other Notes:

1. - * An optional WhereClause can be specified for the table to control the replacement

1. ————————————————————————————-

      CREATE PROCEDURE Remap_Field

              @TableName varchar(50),           -- = 'CustomerGoodsItem'

              @FieldName varchar(255),          -- = 'CategoryID'

              @LookupTableName varchar(50),     -- = 'PricingElement'

              @CustomWhereClause varchar(max) = ''

      AS

      BEGIN

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

1. ————————————————————————————-

go

–

–

–

print 'Creating IDMap Table'

exec Create_IDMapTable;

–

–

–

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'

–

–

–

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

• Backlinks include_pagepage_componentbacklinks