Posted by Danny on 10/01/99 11:23

Erland,

Correct me if I'm mistaken but the values of the columns that make up the
clustered index are only used as the row locator if the index is set to
unique otherwise a uniqueidentifier is used. Either way the values are
still stored in all the nonclustered indexes so wide clustered indexes are
generally unpleasant. Small clustered indexes like a single int can still
make the nonclustered indexes large if when not set to unique.

At least this is always what appears to happen...

Danny


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96AA8DD847A5CYazorman@127.0.0.1...
> (jim_geissman@countrywide.com) writes:
>> One table I manage has a clustered index, and it includes some
>> varchar columns. When it is initially created, all the columns
>> in the clustered index are populated, and then some of the longer
>> varchars are populated through update queries. If the varchar
>> columns are stored outside the clustered structure, then it would
>> make sense to create the clustered index before populating the
>> varchar columns. Otherwise it would make sense to wait, because
>> populating the varchars might cause page splits. Are varchar
>> columns stored on the page along with the fixed-size columns, or
>> are they managed separately with the page containing pointers
>> to them?
>
> As Hugo said, the varchar data is stored within the page.
>
> One should be careful with having to large clustered-index keys. In
> non-clustered indexes, the value of clustered-index key is used as
> row locator. Thus a wide clustered key, also affects the size of
> the non-clustered index.
>
> Of course, if you don't have any non-clustered indexes on the table,
> this is not much of an issue.
>
>
> --
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>

[Back to original message]