LOB data types

Question

Post reply

LOB data types

navtec

Hall of Fame

Points: 3206
More actions
August 24, 2016 at 4:14 am

#320906

Hi,
I am using SQL Server 2008 R2 and just wanted to know which data types are included as LOB.
So far I have:
TEXT
NTEXT
IMAGE
VARCHAR(MAX)
NVARCHAR(MAX)
FILESTREAM
XML
VARBINARY
However, when I run my online index rebuild, it is failing on types of nvarchar as it sees this as being a LOB data type. Would it also include bigint as a LOB data type?
Is Varbinary(max) seen as a LOB type as well?

Viewing 15 posts - 1 through 15 (of 28 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Does this help? I found it by typing the title of your post into a search engine.

John

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

Filestream isn't a datatype. It's an attribute of a varbinary data type column. Bigint is definitely not considered as large object, it's a grand total of 16 8-bytes in size.

Your LOB data types are those that can go over 8kb in size.

TEXT, NTEXT, IMAGE (deprecated SQL 2000 data types)

VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML

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

navtec Hall of Fame Points: 3206 More actions · Answer 3

Ok thanks.

Still very strange why SQL Server would pick up on nvarchar data type as a LOB data type though.

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

navtec (8/24/2016)
Ok thanks.
Still very strange why SQL Server would pick up on nvarchar data type as a LOB data type though.

Go and read the documentation on the (MAX) setting for NVARCHAR/VARCHAR. It changes the storage mechanism for the data from row storage to LOB storage and allows up to 2gb for each NVARCHAR/CHAR column. The TEXT data type is deprecated and will, in theory, go away in a future release of SQL Server. It has been replaced by the (MAX) setting.

"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

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

navtec (8/24/2016)
Still very strange why SQL Server would pick up on nvarchar data type as a LOB data type though.

NVARCHAR(MAX) is a LOB. Any other NVARCHAR is not LOB.

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

navtec Hall of Fame Points: 3206 More actions · Answer 6

Maybe there was a large amount of data in that nvarchar column which caused sql server to treat it as a LOB during online index rebuilding?

I'm using sql server 2008

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

navtec (8/24/2016)
Maybe there was a large amount of data in that nvarchar column which caused sql server to treat it as a LOB during online index rebuilding?

NVARCHAR(MAX) is a LOB. Any other NVARCHAR is not LOB.

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: 398881 More actions · Answer 8

navtec (8/24/2016)
Maybe there was a large amount of data in that nvarchar column which caused sql server to treat it as a LOB during online index rebuilding?
I'm using sql server 2008

Go and read the docs. They describe the behavior pretty well.

Are you hitting an issue?

"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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 9

Reply back with the actual error or warning message you are getting. Are you sure it mentions 'LOB' ?

For example, if you attempt to create an index on a set of variable width columns whose combined size could potentially exceed 900 bytes, then you will get a warning.

create table TableB ( col1 int, col2 date, col3 varchar(1000) );

create index ix_TableB on TableB ( col1, col2, col3 );

Warning! The maximum key length is 900 bytes. The index 'ix_TableB' has maximum length of 1007 bytes.

For some combination of large values, the insert/update operation will fail.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

navtec Hall of Fame Points: 3206 More actions · Answer 10

I am getting this failure message for my online index rebuild:

An online operation cannot be performed for index 'indexname' because the index contains column '' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.

I have checked the index in question and the only column data types it has are

nvarchar

datetime

bigint

uniqueidentifier

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

What do you get if you run this?SELECT

o.name AS TableorViewName

,c.name AS ColumnName

,t.name AS TypeName

,c.max_length

FROM sys.indexes i

JOIN sys.objects o ON i.object_id = o.object_id

JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

JOIN sys.columns c ON o.object_id = c.object_id

JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE i.name = 'indexname'

John

Edit - changed t.max_length to c.max_length, so it gets the max length for that column, not for the data type as a whole

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

navtec (8/24/2016)
I have checked the index in question and the only column data types it has are

Clustered index, or nonclustered?

What's the complete table definition (post the CREATE TABLE) and index definition (post the CREATE INDEX)?

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

navtec Hall of Fame Points: 3206 More actions · Answer 13

I will run that script and also get back the data requested by Gail when I'm logged on tomorrow.

I do think maybe the max_lengths are large for those columns which may be prompting SQL Server to treat them as LOB objects.

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

navtec (8/24/2016)
I do think maybe the max_lengths are large for those columns which may be prompting SQL Server to treat them as LOB objects.

The only time that a varchar/nvarchar column is a LOB data type is when it is a VARCHAR(MAX)/NVARCHAR(MAX). It doesn't matter what the max lengths are, unless it's MAX, it is NOT considered a LOB column.

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