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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 2,254, Visits: 6,194
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: Yesterday @ 5:14 PM
Points: 2,220, Visits: 3,342
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1568624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse