StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server 2005 - odd clustered index size
text
Body
copied!<h3>Existing table structure</h3> <pre><code>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) ) </code></pre> <p>This table has 2 non-clustered indexes, and the following stats:</p> <pre><code>RowCount: 5260744 Data Space: 229.609 MB Index Space: 432.125 MB </code></pre> <p>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.</p> <h3>New table structure</h3> <pre><code>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) ) </code></pre> <p>Still with only 2 non-clustered indexes, here's the new stats:</p> <pre><code>RowCount: 5260744 Data Space: 249.117 MB Index Space: 470.867 MB </code></pre> <h3>Question</h3> <p>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?</p> <p>I rebuilt the indexes before and after the changes, and the fill factor is set to 0 for both structures.</p> <p>The two non-clustered indexes are the same, and were not changed to include the new ID column.</p> <h3>sys.dm_db_index_physical_stats</h3> <p>Stats taken with the ID column</p> <p>Composite clustered index</p> <pre><code>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 </code></pre> <p>INT clustered index</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload