Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table like:

CREATE TABLE grid_rows(
    [grid_row_id] [int] NOT NULL,
    [grid_column_id] [smallint] NOT NULL,
    [decimal_val] [decimal](18, 6) NULL,
    [datetime_val] [datetime] NULL,
    [integer_val] [int] NULL,
    [string_val] [varchar](1024) NULL
)

This table has some 1,037,560 rows

exec sp_spaceused "grid_rows" gives:
rows         reserved    data
1,037,560    461,768KB  302,648KB`

After changing the precision from (18, 6) to (24,6) i.e ALTER TABLE grid_rows ALTER COLUMN decimal_val decimal(24, 6)

exec sp_spaceused "grid_rows" gives:
rows         reserved     data
1,037,560    641,352KB  560,832KB

The space allocated by decimal(18,6) is 9 bytes and that of (24, 6) is 13 bytes. MSDN reference

The reserved space has increased by around 179,584 KB and data space by 260,000KB. Shouldn't it be increased by 1,037,560 * 4/1024 = 4052 KB

share|improve this question
    
Can you clarify what exactly is your question? –  Cezar Apr 12 '13 at 13:13
    
I am able to reproduce the issue, and interestingly the space used will also be different if you create the table with decimal(24, 6) straight from the start: for a table containing 1024 rows, I'm getting 48KB with decimal(18, 6), 56KB with decimal(24, 6) and 72KB with decimal(18, 6) then alter to decimal(24, 6). I have no explanation for this, though. –  Lâm Tran Duy Apr 12 '13 at 13:56
    
If I had to guess, I would say the data pages now contain 2 copies of the field in question. The old copy, which is no longer used, and the new, larger, copy which is used. –  Max Vernon Apr 12 '13 at 14:01
    
@MaxVernon – but then shouldn't it only affect the size of the reserved space, instead of the actual data size? –  Lâm Tran Duy Apr 12 '13 at 14:11
4  
Another thing that may increase table size is page splits. If your pages were quite full then increasing the size of any column could force some of the data to be moved to a new page. –  Aaron Bertrand Apr 12 '13 at 14:11
show 3 more comments

migrated from stackoverflow.com Apr 12 '13 at 13:48

This question came from our site for professional and enthusiast programmers.

2 Answers

Keep in mind that the dropped column is still physically present in the row.

But the real killer is probably that, if this is a heap (table w/o clustered index), then row updates that increase the row size may cause an avalanche of forwarding. Consider that if you start with pages at nearly 100% full then any row increase will cause the row to no longer fit in the page, so a forward record has to be left in place and the row has to be placed somewhere else. Repeat this for every row (extreme case) and you have a pretty nasty scenario.

If is a B-Tree then the same scenario will cause page splits which are better because during the split the copied rows reclaim the space between the rows on the page, which the heap forwarding case does not.

share|improve this answer
add comment

I just used DBCC IND and DBCC PAGE to read the physical data from pages that were storing DECIMAL(4,2) data that was subsequently modified to DECIMAL(8,4) data. The old field is listed now as DROPPED, with another field containing the new data.

        CREATE TABLE TestDec
        (
            DecimalValue DECIMAL(4,2)
        );

        INSERT INTO TestDec VALUES (16.25);
        SELECT * FROM TestDec;

        EXEC sp_spaceused 'TestDec';

        DBCC IND('Test', 'TestDec', -1)

                    /* 20267501 happens to be the page in my database, yours WILL 
                       be different */
        DBCC TRACEON(3604) /* display output from DBCC PAGE */
        DBCC PAGE('Test',1,20267501,3) WITH TABLERESULTS

        ALTER TABLE TestDec ALTER COLUMN DecimalValue DECIMAL(8,4)
        DBCC PAGE('Test',1,20267501,3) WITH TABLERESULTS

enter image description here

Row 46 and 47 show the relevant items.

This indicates that the existing data is copied into a new physical location when the decimal field size is changed.

share|improve this answer
4  
Yes, and this could definitely lead to page splits etc. I believe you need to do a rebuild to recover the space occupied by the dropped columns. –  Aaron Bertrand Apr 12 '13 at 14:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.