Home Forums SQL Server 2012 SQL Server 2012 - T-SQL The storage of VARCHAR(MAX) when null RE: The storage of VARCHAR(MAX) when null

  • May 7, 2014 at 3:53 am

    #1711397

    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:cool:

    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