Anyone who has been either a kid or a parent has probably heard this story before.  The child is sent to clean their room, which to your eyes looks ready for a report to the Board of Health.  Out they come twenty minutes later, with bright shiny faces, to announce that the job is done.  Of course you know better, but as a parent, it’s usually better in these cases to be underestimated.  You express amazement at their efficiency.  You go to check this marvel of cleanliness – and it looks beautiful.

Until you look under the bed.

Many of us grow out of this phase (mostly) and go on to live happy, productive lives.  Some of us even become DBAs, where we reenact our childhoods by compressing our tables and forgetting about our indexes.  This used to be restricted to the users of Enterprise Edition, but as of SQL Server 2016 SP1, many more of us can relive those happy days of yore.

We compress to gain space, correct? So why sacrifice it to our indexes?

Here is a script (complete with demo databases and tables) that will find the culprits and fix them for you.  Put it (minus the demo databases and tables) in a job, and quit worrying about this.  Your tables (and indexes) will be sparkling clean. You’re welcome.

--2016 SP1 feature for Standard, Enterprise before that

CREATE DATABASE Sandbox;
GO

CREATE DATABASE SandboxPartDeux;
GO

USE [Sandbox];
GO


CREATE TABLE [dbo].[CompTableDemo_PAGE]
(
    [Column1] [INT] IDENTITY(1, 1) NOT NULL,
    [Column2] [NVARCHAR](300) NULL,
    [Column3] [NVARCHAR](300) NULL,
    [Column4] [NVARCHAR](300) NULL,
    [Column5] [NVARCHAR](300) NULL
) ON [PRIMARY];
GO

CREATE TABLE [dbo].[CompTableDemo_ROW]
(
    [Column1] [INT] IDENTITY(1, 1) NOT NULL,
    [Column2] [VARCHAR](30) NULL,
    [Column3] [VARCHAR](30) NULL,
    [Column4] [VARCHAR](30) NULL,
    [Column5] [VARCHAR](30) NULL
) ON [PRIMARY];
GO

USE SandboxPartDeux;
GO

CREATE TABLE dbo.AdWorks_ROW
(
    [Column1] [INT] IDENTITY(1, 1) NOT NULL,
    [Column2] [VARCHAR](3) NULL,
    [Column3] [VARCHAR](3) NULL,
    [Column4] [VARCHAR](3) NULL,
    [Column5] [VARCHAR](3) NULL
) ON [PRIMARY];
GO

--Let's load 'em up!

USE Sandbox;
GO

DECLARE @i INT = 0;

WHILE @i < 20000
BEGIN

    INSERT INTO dbo.CompTableDemo_PAGE
    (
        Column2,
        Column3,
        Column4,
        Column5
    )
    VALUES
    (   N'WethePeopleinOrdertoFormaMorePerfectUnionEstablishJusticeandEnsureDomesticTranquilityProvidefortheCommonDefensePromotetheGeneralWelfareandSecuretheBlessingsofLibertyForOurselvesandOurPosterityDoOrdainandEstablishthisConstitutionfortheUnitedStatesofAmerica',                          -- Column1 - nvarchar(250)
        N'ImJustABillYesImOnlyABillAndIfTheyVoteForMeOnCapitolHillWellThenImOffToTheWhiteHouseWhereIWaitInALineWithALotOfOtherBillsForThePresidentToSignAndIfHeSignsMeThenIllBeALAWOhHowIHopeAndPrayThatHeWillButTodayIAmStillJustABill',                                                            -- Column2 - nvarchar(250)
        N'GotHomeFromCampingLastSpringSawPeoplePlacesAndThingsWeBarelyHadArrivedFriendsAskedUsToDescribeThePeoplePlacesAndEveryLastThingSoWeUnpackedOurAdjectivesIUnpackedFrustratingFirstReachedInAndFoundTheWordWorstThenIPickedSoggyAndNextIPickedFoggyAndThenIWasReadyToTellThemMyTale',         -- Column3 - nvarchar(250)
        N'WhenReginaldWasHomeWithFluUhHuhTheDoctorKnewJustWhatToDoHeCuredTheInfectionWithOneSmallInjectionWhileReginaldUtteredSomeInterjectionsOuch!ThatHurts!Yow!ThatsNotFairGivingAGuyAShotDownThere!InterjectionsShowExcitementOrEmotionTheyreGenerallySetApartFromASentenceByAnExplanationPoint' -- Column4 - nvarchar(250)
        );

    INSERT INTO dbo.CompTableDemo_ROW
    (
        Column2,
        Column3,
        Column4,
        Column5
    )
    VALUES
    (   'aaa', -- Column2 - varchar(30)
        'bbb', -- Column3 - varchar(30)
        'ccc', -- Column4 - varchar(30)
        'ddd'  -- Column5 - varchar(30)
        );

    SET @i = @i + 1;

END;

DECLARE @a INT = 0;

WHILE @a < 20000
BEGIN

    INSERT INTO SandboxPartDeux.dbo.AdWorks_ROW
    (
        Column2,
        Column3,
        Column4,
        Column5
    )
    VALUES
    (   'aa', -- Column2 - varchar(3)
        'bb', -- Column3 - varchar(3)
        'cc', -- Column4 - varchar(3)
        'dd'  -- Column5 - varchar(3)
        );

    SET @a = @a + 1;

END;
--Someone watched WAAAAAY too much Schoolhouse Rock back in the day....

SELECT *
FROM dbo.CompTableDemo_ROW;

SELECT *
FROM dbo.CompTableDemo_PAGE;

SELECT *
FROM SandboxPartDeux.dbo.AdWorks_ROW;

/* Public Service Announcement:  If you want to see how to determine whether ROW or PAGE will work better, a general guide would be if you have fixed width rows that aren't 
being used to their full potential, ROW might be a good guess.  For repeating values, PAGE is often a good bet.  The better way, though, is to use some TSQL:

EXEC sp_estimate_data_compression_savings 'dbo', 'CompTableDemo_PAGE', NULL, NULL, 'PAGE';
EXEC sp_estimate_data_compression_savings 'dbo', 'CompTableDemo_ROW', NULL, NULL, 'ROW';

Run them at the same time to compare and see what is REALLY going to give you the savings.
*/

--Back to our regularly scheduled demo, where I am going to ignore what I just said and randomly assign compression. I'm living on the edge today.
 
USE [Sandbox];
ALTER TABLE [dbo].[CompTableDemo_ROW]
REBUILD PARTITION = ALL
WITH
(
    DATA_COMPRESSION = ROW
);

USE [Sandbox];
ALTER TABLE [dbo].[CompTableDemo_PAGE]
REBUILD PARTITION = ALL
WITH
(
    DATA_COMPRESSION = PAGE
);

ALTER TABLE SandboxPartDeux.dbo.AdWorks_ROW
REBUILD PARTITION = ALL
WITH
(
    DATA_COMPRESSION = ROW
);

--Yes, I know I've made some heaps.  It's on purpose.  NOW, let's add an index - or three.

CREATE NONCLUSTERED INDEX ncix_compressiondemo_Column1
ON dbo.CompTableDemo_PAGE (Column1);
CREATE NONCLUSTERED INDEX ncix_compressiondemorow_Column1
ON dbo.CompTableDemo_ROW (Column1);
CREATE NONCLUSTERED INDEX ncix_adworksrow_Column1
ON SandboxPartDeux.dbo.AdWorks_ROW (Column1);

--So, there is the scenario.  We now have compressed tables with an uncompressed indexes.  

--Let's hunt 'em down...

IF OBJECT_ID('tempdb..#CompressionResults') IS NOT NULL
    DROP TABLE #CompressionResults;

IF OBJECT_ID('tempdb..#GenerateScript') IS NOT NULL
    DROP TABLE #GenerateScript;

IF OBJECT_ID('tempdb..#FinalScript') IS NOT NULL
    DROP TABLE #FinalScript;


CREATE TABLE #CompressionResults
(
    DatabaseName sysname NULL,
    SchemaName sysname NULL,
    ObjectName sysname NULL,
    IndexName sysname NULL,
    TableOfIndex sysname NULL,
    DataCompressionDesc sysname NULL
);



DECLARE @CompFindName sysname;
DECLARE @CompFindCursor CURSOR;
DECLARE @CompFindSQL NVARCHAR(MAX);

SET @CompFindCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
ORDER BY name;

OPEN @CompFindCursor;

FETCH NEXT FROM @CompFindCursor
INTO @CompFindName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @CompFindSQL
        = N'USE [' + @CompFindName + N'];
INSERT INTO #CompressionResults
SELECT '''             + @CompFindName
          + N''' AS DatabaseName,
       SCHEMA_NAME(o.schema_id) AS SchemaName,
	   o.name,
       i.name AS IndexName,
	   OBJECT_NAME(i.object_id) AS TableOfIndex,
	   data_compression_desc AS DataCompressionDesc
FROM sys.partitions AS p
    INNER JOIN sys.objects AS o
        ON p.object_id = o.object_id
    INNER JOIN sys.indexes AS i
        ON OBJECT_NAME(i.object_id) = o.name
WHERE o.is_ms_shipped = 0;';
    PRINT @CompFindSQL;
    EXECUTE sp_executesql @CompFindSQL;
    FETCH NEXT FROM @CompFindCursor
    INTO @CompFindName;
END;

CLOSE @CompFindCursor;
DEALLOCATE @CompFindCursor;


SELECT DISTINCT
       CR.DatabaseName,
       CR.SchemaName,
       CR.ObjectName,
       COALESCE(CR3.IndexName, 'TABLE') AS IndexName,
       CR2.TableOfIndex,
       CR2.DataCompressionDesc
INTO #GenerateScript
FROM #CompressionResults AS CR
    INNER JOIN #CompressionResults AS CR2
        ON CR.DatabaseName = CR2.DatabaseName
           AND CR.ObjectName = CR2.TableOfIndex
    INNER JOIN #CompressionResults AS CR3
        ON CR2.DatabaseName = CR3.DatabaseName
           AND CR2.ObjectName = CR3.ObjectName
           AND CR2.IndexName = CR3.IndexName
WHERE CR2.DataCompressionDesc <> 'NONE'
      AND CR3.DataCompressionDesc = 'NONE';

SELECT *
FROM #GenerateScript;

--This is telling us that it has found tables with compression, but indexes that are not.  Let's fix them.  

CREATE TABLE #FinalScript
(
    Script NVARCHAR(MAX)
);

INSERT INTO #FinalScript
(
    Script
)
SELECT N'USE [' + DatabaseName + N']; ALTER INDEX ' + IndexName + N' ON [' + SchemaName + N'].[' + TableOfIndex
       + N'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ' + DataCompressionDesc + N');' AS Script
FROM #GenerateScript;

SELECT 'Executed Script ' + Script AS StatusUpdate
FROM #FinalScript;


DECLARE @FixItName NVARCHAR(MAX);
DECLARE @FixItCursor CURSOR;
DECLARE @FixItSQL NVARCHAR(MAX);

SET @FixItCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT Script
FROM #FinalScript;

OPEN @FixItCursor;

FETCH NEXT FROM @FixItCursor
INTO @FixItName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @FixItSQL = @FixItName;
    --PRINT @FixItSQL;
    EXECUTE sp_executesql @FixItSQL;
    FETCH NEXT FROM @FixItCursor
    INTO @FixItName;
END;

CLOSE @FixItCursor;
DEALLOCATE @FixItCursor;

IF @@TOTAL_ERRORS = 0
BEGIN

    SELECT 'Victory!' AS FinalStatus;

END;


--Go check your indexes before you drop!


EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Sandbox';
GO
USE [Sandbox]

GO
USE [master]

GO
USE [master]
GO
ALTER DATABASE [Sandbox] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO

DROP DATABASE [Sandbox]
GO

DROP DATABASE SandboxPartDeux;
GO

Here is the output:

This part of Spring Cleaning is done.  Next!