LOB data types

Question

LOB data types

Viewing 14 posts - 16 through 28 (of 28 total)

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

ScottPletcher SSC Guru Points: 101119 More actions · Answer 1

GilaMonster (8/24/2016)
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-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

I thought a bigint was 8 bytes (rather than 16).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

ScottPletcher (8/24/2016)
I thought a bigint was 8 bytes (rather than 16).

You are correct. For some reason I was thinking int = 8 when I wrote that.

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

Got to the bottom of it.

There were some included columns in the index which were of type varchar(max) which was what was being picked up by SQL Server. Although the table columns being used by the indexes were fine.

Now I just need to figure out how to bring back index included column data types in my query.

Thanks

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

Yup, included columns are part of the index, just like key columns.

Any query that returns the key columns should return the include columns as well, unless they're getting explicitly filtered out. Include columns are listed in sys.index_columns, just like key columns.

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 5

navtec

Hall of Fame

Points: 3206

August 25, 2016 at 9:58 am

#1897524

Ok thanks to everyone for your help.

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

When I run John's query against my test table (which contains a VARCHAR(MAX) data type column in it), it returns the data type as 'varchar'.

So it seems to miss out the (MAX) from the end of the data types. Same for NVARCHAR(MAX) columns in my table which are displayed simply as NVARCHAR in the query output.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

navtec (8/25/2016)
When I run John's query against my test table (which contains a VARCHAR(MAX) data type column in it), it returns the data type as 'varchar'.
So it seems to miss out the (MAX) from the end of the data types. Same for NVARCHAR(MAX) columns in my table which are displayed simply as NVARCHAR in the query output.

Check max_length. If it is -1 that is the MAX data type.

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

max length for the varchar(max) and nvarchar(max) columns are 8000.

I was hoping they would be -1.....

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

The documentation is incorrect about that for sys.types.

Pull max_length from sys.columns instead, and it should correctly show -1.

Cheers!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

FYI: https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(SYS.TYPES_TSQL);k(SQL12.SWB.TSQLRESULTS.F1);k(SQL12.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true

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

That's worked for sys.columns max length, which is correctly showing -1 for those columns.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

navtec (8/25/2016)
max length for the varchar(max) and nvarchar(max) columns are 8000.
I was hoping they would be -1.....

Not any where I have seen. I am pulling this info on a regular basis doing schema checks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

The values in sys.types show max length. VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) aren't different data types from VARCHAR(n), NVARCHAR(n), or VARBINARY(n) which is why you don't see the max_length of -1 in sys.types. If you look at the documentation for the sys.tables it tells you the a max_length of -1 indicates that those types are defined as a MAX type.

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

Ah yes. My mistake. I've edited the offending post.

John