SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 758
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.
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70148 Visits: 40923
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
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 758
Excellent! Thanks for sharing.

<><
Livin' down on the cube farm. Left, left, then a right.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219477 Visits: 46279
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219477 Visits: 46279
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
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70148 Visits: 40923
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search