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

The storage of VARCHAR(MAX) when null Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:31 AM
Points: 2, Visits: 30
Hi all,
If I have a table
CREATE TABLE [dbo].[logg](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](MAX) NULL)

insert logg (Details) values('')
insert logg (Details) values(null)

Will both statements above access only a single page (as it fits into one page)
or does the VARCHAR(MAX) always put its data on a separate page.
If so, is the null insert treated differently from the '' insert?

Regards Ove
Post #1568339
Posted Wednesday, May 7, 2014 3:53 AM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 1,703, Visits: 4,490
Ove.Kernell (5/7/2014)
Hi all,
If I have a table
CREATE TABLE [dbo].[logg](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](MAX) NULL)

insert logg (Details) values('')
insert logg (Details) values(null)

Will both statements above access only a single page (as it fits into one page)
or does the VARCHAR(MAX) always put its data on a separate page.
If so, is the null insert treated differently from the '' insert?

Regards Ove


If it fits on a single page, as this does, then yes.
You can poke around with a query like the one below
ELECT 
object_name(sp.object_id) AS oname
,ps.used_page_count
,ps.lob_used_page_count
,ps.in_row_data_page_count
,ps.row_overflow_used_page_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions sp
ON ps.partition_id = sp.partition_id

Post #1568349
Posted Wednesday, May 7, 2014 11:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 2,033, Visits: 3,041
By default, yes, they will be stored in the same page.

Someone can, however, override SQL's default behavior, table by table, via "EXEC sp_tableoption" and force SQL to store all LOBs outside of the row.

It's not likely that's been done, but it is possible.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1568624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse