locked
Sparse column size when altering a table RRS feed

  • Question

  •  

    I have been looking at sparse columns to evaluate what space could be saved by altering existing tables.  I was able to see an immediate difference in size when a table was created with a sparse column and populated with data compared to an identical table without the sparse column.  I found that when doing the same steps, but after populating the table altering the column to a sparse column then more space is reserved and used then before altering the column to include sparse?  I have looked for something to explain the increasae in space when altering a column to a sparse column, but have not been able to find an explanation as to why this happens. 

     

    Please let me know if this is a problem in my logic or code.  I am first creating two identical tables, but one has a sparse column while the other does not, both with a single column of a datatype integer, then use a while loop to populate both.  I use sp_spaceused to see the space used and then alter the table that does not have the sparse column to a sparse column and again show the space used and the table now takes more space.  I have done identical steps using different datatypes and multiple columns, but got the same results.

     

    Thank you in advance for your help

     

    Code Snippet

    USE tempdb

    CREATE TABLE nospval(

    spcol   INT NULL);

    GO

     

    DECLARE @int    INT

    SET @int = 1

     

    WHILE @int <= 250000

    BEGIN

        IF @int%10 <> 0

        BEGIN

             INSERT nospval

             VALUES(NULL)

        END

        ELSE

        INSERT nospval

        VALUES(@int)

        SET @int = @int + 1

    END;

    GO

     

    CREATE TABLE spval(

    sprscol     INT SPARSE NULL)

     

    DECLARE @int    INT

    SET @int = 1

     

    WHILE @int <= 250000

    BEGIN

        IF @int%10 <> 0

        BEGIN

             INSERT spval

             VALUES(NULL)

        END

        ELSE

        INSERT spval

        VALUES(@int)

        SET @int = @int + 1

    END;

    GO

     

    --RESULTS AS EXPECTED

    sp_spaceused 'nospval';

    GO

    --Total data is 3144 KB reserved is 3208 KV

    sp_spaceused 'spval';

    GO

    --The total data is 3384 KB, reserved is 3400 KB

     

     

    ALTER TABLE nospval

         ALTER COLUMN spcol INT SPARSE NULL;

    GO

     

    sp_spaceused 'nospval';

    GO    

    --Space data 3944 reserved 3976

    sp_spaceused 'spval';

    GO  

    --Obviously remains unchanged

     

     

     

     

     

    Thursday, August 28, 2008 11:36 AM

Answers

  • Hello, David

      The reason is that just like altering a column type,  change a column into sparse does not re-claim the space.   To do this, you might either use dbcc cleantable for table without clustered index or rebuild clustered index.   Please vist my blog http://blogs.msdn.com/qingsongyao/ for more information about sparse column.

     

    Thursday, September 11, 2008 3:41 AM

All replies

  • I was able to figure out what was occurring.  Using the below code you can view the data page and its attributes before and after altering the column.  The column attribute remains a NULL_BITMAP after ALTERing the table and the space used remains unchanged.  This means that the alter statement completes successfully, but the column attribute is not changed leaving the column space used the same.  I attempted this both with t-sql and with SSMS and got the same result. 

    After completing the below script I began to wonder if altering the column may not affect existing rows, but would be applied to rows inserted after the altering the table, but again found the result to be the same. 

    I am still trying to see if there is something that I am missing, but reported this as a bug.

     

     

     

    Saturday, August 30, 2008 1:59 PM
  • USE tempdb

    /*This function is taken directly from Kalen Delanet’s book The Storage Engine.  It is used to convert the hex value of the data page to a value that can be passed to the DBCC PAGE command*/
     
    CREATE FUNCTION convert_page_nums (@page_num BINARY(6))
       RETURNS VARCHAR(11)
    AS
    BEGIN
     RETURN(CONVERT(VARCHAR(2), (CONVERT(INT, SUBSTRING(@page_num, 6, 1))
         * POWER(2, 8)) +
         (CONVERT(INT, SUBSTRING(@page_num, 5, 1)))) + ':' +
           CONVERT(VARCHAR(11),
        (CONVERT(INT, SUBSTRING(@page_num, 4, 1)) * POWER(2, 24)) +
        (CONVERT(INT, SUBSTRING(@page_num, 3, 1)) * POWER(2, 16)) +
        (CONVERT(INT, SUBSTRING(@page_num, 2, 1)) * POWER(2, 8)) +
        (CONVERT(INT, SUBSTRING(@page_num, 1, 1)))) )
    END;


    CREATE TABLE nonspchars(
    nonsprchar CHAR(35));
    GO

    DECLARE @int INT
    SET @int = 1

    WHILE @int <= 250000
    BEGIN
     IF @int%10 <> 0
     BEGIN
          INSERT nonspchars
          VALUES(NULL)
     END
     ELSE
      INSERT nonspchars
      VALUES('This is not a null value row ' + CONVERT(CHAR(4), @int))
      SET @int = @int + 1
    END;
    GO

    CREATE TABLE sprs(
    nonsprchar CHAR(35) SPARSE NULL);
    GO

    DECLARE @int INT
    SET @int = 1

    WHILE @int <= 250000
    BEGIN
     IF @int%10 <> 0
     BEGIN
          INSERT sprs
          VALUES(NULL)
     END
     ELSE
      INSERT sprs
      VALUES('This is not a null value row ' + CONVERT(CHAR(4), @int))
      SET @int = @int + 1
    END;
    GO

    sp_spaceused 'sprs';
    GO
    sp_spaceused 'nonspchars';
    GO
    --RESULTS
    --sprs ROWS:250000 RESERVED:3976KB DATA:3952KB INDEX_SIZE:8KB UNUSED:16 KB
    --nonspchars ROWS:250000 RESERVED:11464KB DATA:11432KB INDEX_SIZE:8KB UNUSED:24KB

    SELECT OBJECT_NAME(OBJECT_ID) AS Name,
        ROWS,
        type_desc AS page_type_desc,
        total_pages AS Pages,
        first_Page
    FROM sys.partitions p JOIN sys.system_internals_allocation_units a
    ON p.partition_id = a.container_id
    WHERE OBJECT_ID=OBJECT_ID('dbo.sprs');
    GO
    --First page 0xBA0000000100

    SELECT OBJECT_NAME(OBJECT_ID) AS Name,
        ROWS,
        type_desc AS page_type_desc,
        total_pages AS Pages,
        first_Page
    FROM sys.partitions p JOIN sys.system_internals_allocation_units a
    ON p.partition_id = a.container_id
    WHERE OBJECT_ID=OBJECT_ID('dbo.nonspchars');
    GO
     
    --First page 0x9D0000000100

    SELECT dbo.convert_page_nums(0xBA0000000100)
    --1:186
    SELECT dbo.convert_page_nums(0x9D0000000100)
    --1:157
     
    DBCC TRACEON(3604)

    DBCC PAGE(tempdb, 1, 186, 1)--sparse column
    --Example of null value row in col defined as sparse
    /*Slot 3, Offset 0x7b, Length 9, DumpStyle BYTE
    Record Type = PRIMARY_RECORD         Record Attributes =                  Record Size = 9
    Memory Dump @0x62D5C07B
    00000000:   00000400 01000800 00†††††††††††††††††.........  */

    DBCC PAGE(tempdb, 1, 157, 1)
    --Example of null value row in a column not defined as sparse
    /*Slot 6, Offset 0x15c, Length 42, DumpStyle BYTE
    Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 42
    Memory Dump @0x62D5C15C
    00000000:   10002700 00000000 00000000 e8c15a0d †..'.........èÁZ.        
    00000010:   b8a04e0d 01000000 38605a0d 00000000 †¸ N.....8`Z.....        
    00000020:   00000000 88a14e01 0001†††††††††††††††.....¡N... */
       
    ALTER TABLE nonspchars
         ALTER COLUMN nonsprchar CHAR(35) SPARSE NULL;

    DBCC PAGE(tempdb, 1, 157, 1)
    --Post alter still shows NULL_BITMAP
    /*Slot 0, Offset 0x60, Length 42, DumpStyle BYTE
    Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 42
    Memory Dump @0x63F2C060
    00000000:   10002700 00000000 00000000 e8c15a0d †..'.........èÁZ.        
    00000010:   b8a04e0d 01000000 38605a0d 00000000 †¸ N.....8`Z.....        
    00000020:   00000000 88a14e01 0001†††††††††††††††.....¡N...               */


    DROP TABLE sprs;
    GO

    DROP TABLE nonsprchar;
    GO

    Saturday, August 30, 2008 2:07 PM
  • Hello, David

      The reason is that just like altering a column type,  change a column into sparse does not re-claim the space.   To do this, you might either use dbcc cleantable for table without clustered index or rebuild clustered index.   Please vist my blog http://blogs.msdn.com/qingsongyao/ for more information about sparse column.

     

    Thursday, September 11, 2008 3:41 AM
  • Thank you for your response.  I attempted DBCC CLEANTABLE, which did not reclaim any space, but after creating a clustered index and using DBCC REINDEX the space was reclaimed.

     

    Thank you again.

     

    Friday, September 12, 2008 10:25 AM