Clustered index and varchar

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?

Thanks,
Jim GeissmanOn 5 Aug 2005 15:09:48 -0700, jim_geissman@.countrywide.com wrote:

(snip)
>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?

Hi Jim,

Varchar (and nvarchar, varbinary) columns are stored on the data page.
Text (and ntext, image) columns are stored on seperate pages, with only
a pointer on the data page. Unless the "text in row" option is set for
the table.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo.

Jim|||(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/techin.../2000/books.asp|||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/techin.../2000/books.asp|||Danny (someone@.nowhere.com) writes:
> 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...

The clustered index is always used as a row locator.

When the clustered index is not unique, SQL Server adds a 32-bit
"uniquifier". That is, not a 128-bit uniqueidentifier.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp