Clustered index and varchar

[jim_geissman]

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 Geissman

[Hugo Kornelis]

On 5 Aug 2005 15:09:48 -0700, ji**********@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)

[jim_geissman]

Thanks, Hugo.

Jim

[Erland Sommarskog]

(ji**********@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, es****@sommarskog.se

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

[Danny]

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" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

(ji**********@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, es****@sommarskog.se

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

[Erland Sommarskog]

Danny (so*****@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, es****@sommarskog.se

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