varchar(N), N can be 1 through 8000 or MAX(2GB). Why not 1 - 2GB?

Question

varchar(N), N can be 1 through 8000 or MAX(2GB). Why not 1 - 2GB?

Tobar

SSCarpal Tunnel

Points: 4876
More actions
May 2, 2013 at 6:20 am

#293417

Anyone have an idea why the varchar data type is limited to be 1 through 8000 or MAX(2GB) but none of the numbers between 8001 and 2GB?
Just one of those little gremlins of "Huh" that vex me.
Hope you all have a great day. Dodged 5-13 inches of snow myself, so mine already has a good start. 🙂
<><
Livin' down on the cube farm. Left, left, then a right.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Tobar (5/2/2013)
Anyone have an idea why the varchar data type is limited to be 1 through 8000 or MAX(2GB) but none of the numbers between 8001 and 2GB?
Just one of those little gremlins of "Huh" that vex me.
Hope you all have a great day. Dodged 5-13 inches of snow myself, so mine already has a good start. 🙂

It's a byproduct of the design for memory usage.

basically it tell SQL whether it can store a value in the data in a single page of data, or if it needs to use alternative storage for BLOBS.

SQL tries to cache items in pages of RAM memory so that subsequent queries for the same data is faster.

a page is 8,060 bytes. anything larger than that needs to be stored in either multiple pages, or a pointer gets stored and it points to a location of the data on the disk instead.

so that's 128 pages per megabyte.

so say on a SQL server, the most SQL can cache/keep ready for fast access would be 131072 pages per gigabyte of RAM.

it would adversely affect performance if a lot of 2 gig items were stored in the memory pages of RAM, so a pointer might be in the page,and if you request the data, SQL will go thru the extra step behind the scenes of reading the data from disk.

think of this as the same as having reference books on top of my desk for instant access, vs, a sheet of paper indexing where the book is on a bookshelf, so i can go get it if i need it.

if your desk was covered with reference books, you would be less efficient doing your normal work. better to "know" where the data is if you need it, vs cluttering up your workspace with something you don't need as often as other items.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

Excellent! Thanks for sharing.

<><
Livin' down on the cube farm. Left, left, then a right.

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

Varchar(N) for N 1..8000 can be stored 'in-page', that means with the rest of the row. Over that, it has to be stored in a list of LOB pages (each 8k in size), so once you go over 8000 characters there's no limit other than the maxint (int being the data type that stores the length), that's roughly 2 billion, so 2GB.

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

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

Lowell (5/2/2013)
it would adversely affect performance if a lot of 2 gig items were stored in the memory pages of RAM, so a pointer might be in the page,and if you request the data, SQL will go thru the extra step behind the scenes of reading the data from disk.

It's not really abut memory, the LOB pages are processed through the data cache just like any other pages with all the aging algorithms applying to them

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

Lowell SSC Guru Points: 323518 More actions · Answer 5

thank you Gail!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!