469,964 Members | 1,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

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 Geissman

Aug 5 '05 #1
5 5875
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)
Aug 5 '05 #2
Thanks, Hugo.

Jim

Aug 5 '05 #3
(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

Aug 6 '05 #4
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

Aug 8 '05 #5
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

Aug 8 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jim | last post: by
1 post views Thread by Steve_CA | last post: by
1 post views Thread by anonieko | last post: by
2 posts views Thread by Lyle Fairfield | last post: by
5 posts views Thread by pb648174 | last post: by
1 post views Thread by Curt | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.