Search code examples
sql-serverclustered-index

SQL Server 2005 - odd clustered index size

Existing table structure

CREATE TABLE [MYTABLE](
    [ROW1] [numeric](18, 0) NOT NULL,
    [ROW2] [numeric](18, 0) NOT NULL,
    [ROW3] [numeric](18, 0) NOT NULL,
    [ROW4] [numeric](18, 0) NULL,
    CONSTRAINT [MYTABLE_PK] PRIMARY KEY CLUSTERED ([ROW1] ASC, [ROW2] ASC, [ROW3] ASC)
)

This table has 2 non-clustered indexes, and the following stats:

RowCount:    5260744
Data Space:  229.609 MB
Index Space: 432.125 MB

I wanted to reduce the size of the indexes, and use a surrogate primary key as the clustered index, instead of the natural composite key.

New table structure

CREATE TABLE [dbo].[TEST_RUN_INFO](
    [ROW1] [numeric](18, 0) NOT NULL,
    [ROW2] [numeric](18, 0) NOT NULL,
    [ROW3] [numeric](18, 0) NOT NULL,
    [ROW4] [numeric](18, 0) NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [MYTABLE_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
)

Still with only 2 non-clustered indexes, here's the new stats:

RowCount:    5260744
Data Space:  249.117 MB
Index Space: 470.867 MB

Question

Can someone account for how a clustered index using 3 NUMERIC(18,0) columns is smaller than a clustered index using a single INT column?

I rebuilt the indexes before and after the changes, and the fill factor is set to 0 for both structures.

The two non-clustered indexes are the same, and were not changed to include the new ID column.

sys.dm_db_index_physical_stats

Stats taken with the ID column

Composite clustered index

INDEX   TYPE            DEPTH   LEVEL   PAGECOUNT   RECORDCOUNT RECORDSIZE  
1       CLUSTERED       3       0       31884       5260744     47
1       CLUSTERED       3       1       143         31884       34
1       CLUSTERED       3       2       1           143         34
5       NONCLUSTERED    3       0       27404       5260744     40
5       NONCLUSTERED    3       1       167         27404       46
5       NONCLUSTERED    3       2       1           167         46
6       NONCLUSTERED    3       0       27400       5260744     40
6       NONCLUSTERED    3       1       164         27400       46
6       NONCLUSTERED    3       2       1           164         46

INT clustered index

INDEX   TYPE            DEPTH   LEVEL   PAGECOUNT   RECORDCOUNT RECORDSIZE  
1       CLUSTERED       3       0       31887       5260744     47
1       CLUSTERED       3       1       54          31887       11
1       CLUSTERED       3       2       1           54          11
5       NONCLUSTERED    4       0       29893       5260744     44
5       NONCLUSTERED    4       1       198         29893       50
5       NONCLUSTERED    4       2       3           198         50
5       NONCLUSTERED    4       3       1           3           50
6       NONCLUSTERED    4       0       29891       5260744     44
6       NONCLUSTERED    4       1       193         29891       50
6       NONCLUSTERED    4       2       2           193         50
6       NONCLUSTERED    4       3       1           2           50
Solution

  • The clustered index leaf pages include all the columns of the table (not just the key columns). By adding a surrogate primary key you have just increased the length of all rows in the leaf pages by 4 bytes. Multiply that out by 5,260,744 rows and that equals an additional 20 MB to store the ID column.

    The key is narrower however so you may well have fewer non leaf level pages (use sys.dm_db_index_physical_stats to see this) and as the clustered index key is used as the row locator in the non clustered indexes this can make those smaller (but less covering) too.