clustered index

Hi,
I have a table with 5 ints, 3 of them in an index.
I tested with a unclustered index and a clustered index.
The clustered one took more space!
? I expected the clustered one to take less space!
Can someone explain?
Thanx
FrankThe leaf pages of the clustered index contain ALL the columns of the table
so a clustered index will usually be larger than a non-clustered one.
--
David Portas
SQL Server MVP
--|||The leaf level of clustered index contains data. In most situation, it
should occupy more space than non-clustered index.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <frank@.frank.com> wrote in message
news:crhdfr$4g1$1@.news6.zwoll1.ov.home.nl...
> Hi,
> I have a table with 5 ints, 3 of them in an index.
> I tested with a unclustered index and a clustered index.
> The clustered one took more space!
> ? I expected the clustered one to take less space!
> Can someone explain?
> Thanx
> Frank
>|||Clustered index is basically a table organized in b-tree structure so it is
basically size of table whereas non-clustered index only contians these 3
columns + rowid or clustered index so smaller than clustered index
"Frank" wrote:
> Hi,
> I have a table with 5 ints, 3 of them in an index.
> I tested with a unclustered index and a clustered index.
> The clustered one took more space!
> ? I expected the clustered one to take less space!
> Can someone explain?
> Thanx
> Frank
>
>|||Thanks all,
but from the sqlbook I understand that the table IS the index. The book
says: the table is sorted on the the keyvalues or: the leaf (lowest) level
is actually the table. So no extra space is needed, only for the branches
leading to the leafs (dat).
Frank
"harvinder" <harvinder@.discussions.microsoft.com> schreef in bericht
news:F0F13A73-A7F7-4E9D-A381-79CD83301226@.microsoft.com...
> Clustered index is basically a table organized in b-tree structure so it
> is
> basically size of table whereas non-clustered index only contians these 3
> columns + rowid or clustered index so smaller than clustered index
> "Frank" wrote:
>> Hi,
>> I have a table with 5 ints, 3 of them in an index.
>> I tested with a unclustered index and a clustered index.
>> The clustered one took more space!
>> ? I expected the clustered one to take less space!
>> Can someone explain?
>> Thanx
>> Frank
>>|||I did some extra homework. My first test was creating a table and after
filling it I put an index on it. The clustered index turned out to be
bigger.
Second test I created the table with the clustered index and then filling
it. Now it was like I expected. The used dbspace is about equal to the data
alone. A test with a table with an unclustered index and then filling it
used up space to about twice the table data.
So my conclusion is: a clustered index is much smaller.
Frank
"Frank" <frank@.frank.com> schreef in bericht
news:crhdfr$4g1$1@.news6.zwoll1.ov.home.nl...
> Hi,
> I have a table with 5 ints, 3 of them in an index.
> I tested with a unclustered index and a clustered index. The clustered one
> took more space!
> ? I expected the clustered one to take less space!
> Can someone explain?
> Thanx
> Frank
>|||> So my conclusion is: a clustered index is much smaller.
It depends on what you include in the index. A the leaf level of a clustered index is the data
pages. On top of that you have the non-leaf pages (which is rarely more than a few percent compared
to the data pages). So if you include the data pages, the index is table size plus non-leaf level.
Say you have a table of 100Mb and then create a clustered index, after that the table (with the
clustered index as the leaf level of the clustered index is the data pages) will occupy perhaps
103-107 Mb.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Frank" <frank@.frank.com> wrote in message news:crhgtj$jsi$1@.news2.zwoll1.ov.home.nl...
>I did some extra homework. My first test was creating a table and after filling it I put an index
>on it. The clustered index turned out to be bigger.
> Second test I created the table with the clustered index and then filling it. Now it was like I
> expected. The used dbspace is about equal to the data alone. A test with a table with an
> unclustered index and then filling it used up space to about twice the table data.
> So my conclusion is: a clustered index is much smaller.
> Frank
> "Frank" <frank@.frank.com> schreef in bericht news:crhdfr$4g1$1@.news6.zwoll1.ov.home.nl...
>> Hi,
>> I have a table with 5 ints, 3 of them in an index.
>> I tested with a unclustered index and a clustered index. The clustered one took more space!
>> ? I expected the clustered one to take less space!
>> Can someone explain?
>> Thanx
>> Frank
>|||> So no extra space is needed, only for the branches leading to the leafs
> (dat).
You have answered your own question. Extra space IS needed. Both the data
and the B-tree are held within the clustered index.
--
David Portas
SQL Server MVP
--|||On Wed, 5 Jan 2005 20:37:59 +0100, Frank wrote:
>Thanks all,
>but from the sqlbook I understand that the table IS the index.
Hi Frank,
Yes. Therefore, the size reported for the clustered index is the size of
all leaf pages (the table data) plus the size of the root and intermediate
levels of the B-tree.
A nonclustered index is smalle, since the leaf pages of this index store
only the indexed columns and a row locator (the columns of the clustered
index, if one is available, else a physical locator). Since the size of
indexed columns + columns in clustered index is typically less than the
size of the complete row, the nonclustered index itself (which is only the
index) takes less space than the clustered index (which is actually the
table plus the index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)