Varchar(Max) storage

Question

Varchar(Max) storage

Arsh

SSCertifiable

Points: 6354
More actions
March 18, 2019 at 7:54 am

#371746

Dear Experts,
Have a confusion regards to the storage capacity of varchar(max). My question is , when 8000 bytes (the whole page) is what's reserved by
SQL server for varchar(max) declaration , how does the 2 GB content is stored in one 8KB page ? and how does 8000 characters equal 2 GB of storage? thank u in advance.
Best Regards...Arsh

Viewing 13 posts - 1 through 12 (of 12 total)

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

yelouati SSC Enthusiast Points: 108 More actions · Answer 1

it includes a pointer to an out of row space where it is stored. That said, it is sometimes in row, especially when it is small enough to fit in row.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Arsh - Monday, March 18, 2019 7:54 AM
Dear Experts,
Have a confusion regards to the storage capacity of varchar(max). My question is , when 8000 bytes (the whole page) is what's reserved by
SQL server for varchar(max) declaration , how does the 2 GB content is stored in one 8KB page ? and how does 8000 characters equal 2 GB of storage? thank u in advance.
Best Regards...Arsh

The large entries are stored in ROW_OVERFLOW_DATA allocation units, the SQL Server dynamically manages this and adds a pointer to the originating page.
😎

More information here, Pages and Extents Architecture Guide

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Eirikur Eiriksson - Monday, March 18, 2019 8:20 AM
Arsh - Monday, March 18, 2019 7:54 AM
Dear Experts,
Have a confusion regards to the storage capacity of varchar(max). My question is , when 8000 bytes (the whole page) is what's reserved by
SQL server for varchar(max) declaration , how does the 2 GB content is stored in one 8KB page ? and how does 8000 characters equal 2 GB of storage? thank u in advance.
Best Regards...Arsh
The large entries are stored in ROW_OVERFLOW_DATA allocation units, the SQL Server dynamically manages this and adds a pointer to the originating page.
😎
More information here, Pages and Extents Architecture Guide

To be totally accurate, LOBs are stored in "LOB_DATA", not "ROW_OVERFLOW_DATA" (although both are handled in mostly the same way).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Jeff Moden - Monday, March 18, 2019 8:38 AM
Eirikur Eiriksson - Monday, March 18, 2019 8:20 AM
Arsh - Monday, March 18, 2019 7:54 AM
Dear Experts,
Have a confusion regards to the storage capacity of varchar(max). My question is , when 8000 bytes (the whole page) is what's reserved by
SQL server for varchar(max) declaration , how does the 2 GB content is stored in one 8KB page ? and how does 8000 characters equal 2 GB of storage? thank u in advance.
Best Regards...Arsh
The large entries are stored in ROW_OVERFLOW_DATA allocation units, the SQL Server dynamically manages this and adds a pointer to the originating page.
😎
More information here, Pages and Extents Architecture Guide
To be totally accurate, LOBs are stored in "LOB_DATA", not "ROW_OVERFLOW_DATA" (although both are handled in mostly the same way).

Thanks for the correction Jeff!
😎

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Shirting gears a bit... very few people realize the change they made way back in 2005 concerning LOBs...

Prior to 2005, the default was to store LOB data "out of row" no matter what size it happened to be. In 2005, the default is to store LOB data "in-row", which is a bit of a misnomer. It only stores LOB data "in-row" (which actually means either the HEAP or Clustered Index) if it's < 8KB AND it fits in the row after all the other columns present have enough room. Unfortunately, THAT HAS A DEVASTATING EFFECT ON PERFORMANCE OF NON-LOB QUERIES IF THEY NEED TO DO A SCAN AND THEY ALSO MAKE A TRAIN WRECK OF PAGE DENSITY BECAUSE OF SUPER SHORT "TRAPPED" ROWS.

I'm actually in the process of finishing a presentation ("Black Arts" Index Maintenance #3 - Defragged by Default) on the subject and how to fix the raft of problems than "In-Row" LOBs cause as well as how to prevent the "ExpAnsive" updates they can cause which prevents a so-called "Append Only" Clustered Index from becoming "Defragmented by Default".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 6

Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...

Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

David Burrows - Tuesday, March 19, 2019 8:04 AM
Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...
Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛

Heh... far better than "skirting". :D:D:D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DinoRS SSCrazy Points: 2683 More actions · Answer 8

Jeff Moden - Tuesday, March 19, 2019 8:34 AM
David Burrows - Tuesday, March 19, 2019 8:04 AM
Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...
Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛
Heh... far better than "skirting". :D:D:D

I â€¦ must â€¦ add â€¦ to â€¦ this!
If you meant you're shitting gears and you drive a non U.S. car I would have a chat with the gearbox manufacturer. :doze: 😎

Next up in U.S. sold cars: Warning Plate - Do not under any circumstances take a dump on your gearbox while shifting gears!

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

David Burrows - Tuesday, March 19, 2019 8:04 AM
Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...
Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛

JAKG (Just Another Keyboard Gone)
😎

shirting

/ËˆÊƒÉ™ËtÉªÅ‹/

noun

a material for making shirts, especially a fine cotton in plain colours or incorporating a traditional woven stripe.
"Oxford is shirting fabric with a lustrous, soft finish"

David Burrows SSC Guru Points: 65142 More actions · Answer 10

Eirikur Eiriksson - Tuesday, March 19, 2019 9:14 AM
David Burrows - Tuesday, March 19, 2019 8:04 AM
Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...
Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛
JAKG (Just Another Keyboard Gone)
😎
shirting
/ËˆÊƒÉ™ËtÉªÅ‹/
noun
a material for making shirts, especially a fine cotton in plain colours or incorporating a traditional woven stripe.
"Oxford is shirting fabric with a lustrous, soft finish"

Nah PBKAC :Whistling:

Far away is close at hand in the images of elsewhere.
Anon.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

David Burrows - Tuesday, March 19, 2019 10:25 AM

Eirikur Eiriksson - Tuesday, March 19, 2019 9:14 AM
David Burrows - Tuesday, March 19, 2019 8:04 AM
Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...
Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛
JAKG (Just Another Keyboard Gone)
😎
shirting
/ËˆÊƒÉ™ËtÉªÅ‹/
noun
a material for making shirts, especially a fine cotton in plain colours or incorporating a traditional woven stripe.
"Oxford is shirting fabric with a lustrous, soft finish"

Nah PBKAC :Whistling:

chapeau mon ami
😎

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

DinoRS - Tuesday, March 19, 2019 8:49 AM
Jeff Moden - Tuesday, March 19, 2019 8:34 AM
David Burrows - Tuesday, March 19, 2019 8:04 AM
Jeff Moden - Monday, March 18, 2019 10:22 AM
Shirting gears a bit...
Shirting... really :crazy:
If you're shirting instead of shifting, we're gonna have words my ol' friend 😛
Heh... far better than "skirting". :D:D:D
I â€¦ must â€¦ add â€¦ to â€¦ this!
If you meant you're shitting gears and you drive a non U.S. car I would have a chat with the gearbox manufacturer. :doze: 😎
Next up in U.S. sold cars: Warning Plate - Do not under any circumstances take a dump on your gearbox while shifting gears!

I'm old. It's amazing what passes. 😀 The fun part is, I don't usually remember what I ate until it passes. :D:D:D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)