Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

varchar(N), N can be 1 through 8000 or MAX(2GB). Why not 1 - 2GB? Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 6:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1448779
Posted Thursday, May 2, 2013 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1448781
Posted Thursday, May 2, 2013 6:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Excellent! Thanks for sharing.

<><
Livin' down on the cube farm. Left, left, then a right.
Post #1448782
Posted Thursday, May 2, 2013 6:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 40,174, Visits: 36,571
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 2008, MVP
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

Post #1448784
Posted Thursday, May 2, 2013 6:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 40,174, Visits: 36,571
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 2008, MVP
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

Post #1448787
Posted Thursday, May 2, 2013 7:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
thank you Gail!


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1448800
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse