473,583 Members | 3,061 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6058
On 5 Aug 2005 15:09:48 -0700, ji**********@co untrywide.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**********@c ountrywide.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****@sommarsk og.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 uniqueidentifie r 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****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
(ji**********@c ountrywide.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****@sommarsk og.se

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

Aug 8 '05 #5
Danny (so*****@nowher e.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 uniqueidentifie r 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 uniqueidentifie r.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

Aug 8 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2110
by: Jim | last post by:
I put a clustered index on a table with 2 columns -nationalityid int autoincrement PK -nationality varchar(50) the clustered index is on nationality however; when i do a select i still get a record set back ordered by
1
2255
by: Steve_CA | last post by:
Hi, The more I read, the more confused I'm getting ! (no wonder they say ignorance is bliss) I just got back from the bookstore and was flipping through some SQL Server Administration books. One says, that to get the best query performance, youi do two things:
2
1516
by: Danny | last post by:
Is there any way to update a column in a clustered index without incurring the cost of reordering. Example: Create table TableX ( Col1 int, Col2 smalldatetime, Col3 varchar(10)) go create clustered indext ix_test on tableX (Col2, Col1)
2
2346
by: Miss Livvy | last post by:
Would it be OK to use varchar(5) instead of char(5) as the first field of a composite clustered index? My gut tells me that varchar would be a bad idea, but I am not finding much information on this topic on this when I Google it. Currently the field is Char(4), and there is a need to increase it to hold 5 characters. TIA
6
8668
by: bala | last post by:
hi guru's would appreciate if someone could show how to list all the clustered indexes in the database. if it can done as a output of single query it would be fine. the output should be the table name, column name and clustered index name. thanx bala
1
2302
by: anonieko | last post by:
A lot of detailed discussion explains the difference between clustered and non-clustered indexes. But very few 'clarifies' why the term used is 'clustered'. Well, once and for all, this is my take. *** The 'CLUSTERED' adjective refers to the INDEX being clustered (set adjacent) to the DATA. This means if you found the index, the data...
2
4336
by: Lyle Fairfield | last post by:
'Property Clustered As Boolean 'Member of DAO.Index Private Sub IsThereaClusteredIndex() Dim tdf As DAO.TableDef Dim idx As DAO.Index For Each tdf In DBEngine(0)(0).TableDefs For Each idx In tdf.Indexes Debug.Print tdf.Name, idx.Name, idx.Primary, idx.Clustered Next idx
5
9714
by: pb648174 | last post by:
I've been doing a bit of reading and have read in quite a few places that an identity column is a good clustered index and that all or at least most tables should have a clustered index. The tool I used to generate tables made them all with non clustered indexes so I would like to drop all of them and generate clustered indexes. So my...
1
1848
by: Curt | last post by:
What is the difference please?
0
7890
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8168
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8316
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7926
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8187
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6573
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5367
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3837
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2322
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.