May 7, 2014 at 3:32 am
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
May 7, 2014 at 3:53 am
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
May 7, 2014 at 11:34 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply