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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply