Defrag Data within file (nvarchar, etc)

Hi All,
My boss has asked me about defraging data within a datafile. Our database has a lot of nvarchar fields, and he was wondering if it is possible to defrag this kind of data (our system allows for many updates of these fields, possibly causeing fragmentation if the new value is longer than the old).

From what i have read on this site and elsewhere, it doesnt sound like this is possible- is this really the case?

Thanks
Ben

i dont think i am talking about the same thing...let me explain a bit more...

Say i have a table that has several columns, one of which is colA NVARCHAR(50).

When data is inserted into a row, the value inserted into colA is 'Mountain'. As i understand it, since it is an nvarchar column, SQL server will allocate enough space to store the value 'Mountain' (8 characters). Say this value is changed to 'Mountain Designs' so it is now 16 characters long. The second 8 characters wont be stored in the same physical place on the disk as SQL Server had to find space for the additional 8 characters when the update was done, so to return the value 'Mountain Designs' both locations on the drive would need to be read. What i want to do is 'defrag' the data so it is stored in the one physical location

Do you have clustered indexes on tables you want to defragment? Do you have indexes on nvarchar columns mentioned? If so, run dbcc showcontig to see how much data and indexes are fragmented. When you identify fragmented ones you can rebuild/defragment related indexes. Since data are stored in a leaf level of clustered index, defragmenting clustered index will defragment data in the table.

mmarovic, not all the nvarchars are included in the indexes

Frank, we thought the data would need to be defraged to improve read time- if all the data in one field was in the same place surely it would be quicker to read from disk than if it is split? though from what Satya said, this isnt the case?

They are if you have clustered index on each table involved. Does it mean there is a table with nvarchar columns without clustered index ?

all the tables have clustered indexes- and i would say with a reasonable amount of certainty the clustered indexes are all on the primary keys. eg tbl_Applicant<br /><br /><pre id="code"><font face="courier" size="2" id="code">CREATE TABLE [tbl_Applicant] (<br />[TransactionId] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,<br />[ApplicantType] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,<br />[Title] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,<br />[FirstName] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,<br />[MiddleName] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,<br />[LastName] [nvarchar] (4<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE Latin1_General_CI_AS NULL ,<br />[DOB] [datetime] NULL ,<br />[Gender] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,<br />[MaritalStatus] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,<br />[Dependants] [int] NULL ,<br />[EmailAddress] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,<br />[ResidencyStatus] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,<br />[Nationality] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,<br />[NoOtherCommitments] [bit] NULL ,<br />[LastEnquiry] [datetime] NULL ,<br />[ContainerName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,<br />[UniqueId] [bigint] IDENTITY (1, 1) NOT NULL ,<br />CONSTRAINT [PK_tbl_Applicant] PRIMARY KEY CLUSTERED <br />(<br />[TransactionId],<br />[ApplicantType]<br />) WITH FILLFACTOR = 85 ON [PRIMARY] ...etc) ON [PRIMARY]<br />GO</font id="code"></pre id="code"><br />But the other nvarchars (name for instance) are not part of the clustered index...maybe i am misunderstanding how clustered indexes work??

Leaf level of clustered index is row itself. You are right that clustered index is ordered by index columns and also other levels (then leaf) contain only columns clustered index is defined on. However, for defragmentation purpose it is enough to have clustered index. Defragmenting clustered index means data pages will be defragmented. The only exception would be blog types (like text, ntext,...), but it is not your problem.

Thankyou! i think i follow now! Will just have to convince the boss defragging the indexes is enough...

Thanks Frank...will see what i can do- unfortunately (as i have proved myself through my 101 posts (yay for me!)) just cause you read it in BOL doesnt mean you understand it!

very true! the good news is that i have explained to/convinced him that defragging the indexes is all we can do, and he is happy with that.<br /><br />Thanks again everyone...maybe microsoft needs a BOL for dummies <img src='/community/emoticons/emotion-1.gif' alt='' />