Varchar(MAX) column as included column in an index

Question

Post reply

Varchar(MAX) column as included column in an index

Matthew Darwin

SSCertifiable

Points: 5513
More actions
October 29, 2014 at 6:17 am

#291984

Hi All,
I was wondering what exactly is stored in the leaf level of a non clustered index if a varchar(max) column is added as an included column? Does it duplicate the LOB data, or contain a pointer to the LOB pages in the cluster?
When looking at the leaf page using DBCC PAGE('db_name', fileid, pageid,3), the column shows only the value "varchar(max)", when using DBCC PAGE ('db_name', fileid,pageid,1) it seems that the string data is held on the index if it wouldn't have overflowed?
Just wondering how this impacts the performance and size of an nci when using varchar(max).
Regards
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Matthew Darwin (10/29/2014)
Does it duplicate the LOB data

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Which means that it does impact the non-clustered index. Is that necessarily bad? No, but it's something to take into account when choosing whether or not to include, or even use, that data type.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 3

OK thanks. So, just to clarify how the out of row data for a varchar(max) works on an nci with it in the included column; is it as follows for a return of a set of data:-

Perform a scan against a row in a non clustered index, navigate the index tree from root to leaf, then follow an additional pointer to the page(s) that contain the LOB data; this would therefore be similar to the behaviour of a key lookup on a nonclustered index? So, we're saving the hop to the clustered index, but still having to follow an out of order seek to each page of data that contains the LOB data?

Or am I way off the mark?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

The read out to get the extra data is going to happen one way or another. Even if you have a lookup back to the cluster, you'll have to read out to the external storage.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 5

OK, thanks for the clarification.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]