SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understandig row length calculation for sparse columns


Understandig row length calculation for sparse columns

Author
Message
Aashini Shah
Aashini Shah
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 231
Hello,

I hope someone can explain the internals for row length.

We have a 3rd party vendor table with following definition: 7 non-sparse columns and 340 varchar(max) sparse columns:

CREATE TABLE [dbo].[ReportSparseData](
[ReportSparseDataId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ReportMainId] [int] NOT NULL,
[ReportYearId] [char](2) NOT NULL,
[UserId] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[DateLstMod] [datetime] NOT NULL,
[ts] [timestamp] NOT NULL,
[C1] [varchar](max) SPARSE NULL,
[C2] [varchar](max) SPARSE NULL,
[C3] [varchar](max) SPARSE NULL,
[C4] [varchar](max) SPARSE NULL,
[C5] [varchar](max) SPARSE NULL,
[C6] [varchar](max) SPARSE NULL,
--

--

--
[C339] [varchar](max) SPARSE NULL,
[C340] [varchar](max) SPARSE NULL,
CONSTRAINT [Pk_ReportSparseData_[ReportSparseDataId] PRIMARY KEY CLUSTERED
(
[ReportSparseDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-------------------------------------------------------------------
Question 1: What would be the row size for this table definition?
-------------------------------------------------------------------

We need to start replicating this table to another environement for reporting. Our publisher, subscriber and distributor are sql server 2008 r2.

When I try to create snapshot for this table, it fails with the following error:
Message: Cannot create a row of size 8875 which is greater than the allowable maximum row size of 8060.
Stack: (Source: MSSQLServer, Error number: 511)

------------------------------------------------------------------------------------------------
Question 2: How do I get transactional replication to work for this table? Is it possible with all columns? --------------------------------------------------------------------------------------------------

Say If every page has row overflow allocation pointer for all columns, then also page size will be (340*24) + 38 bytes = 8198 for the first 7 columns. That still does not add up to 8875 bytes.

I checked the data in the table. All rows have in-line data and no row-overflow allocation despite varchar(max) data type. We added total bytes for all columns for each row in the table. The max row size was 3071 bytes.

------------------------------------------------------------------
Question 3: where does this total 8875 bytes coming from?
------------------------------------------------------------------

I just would like to clear my concepts here on what would be the row size for the table definition I provided? What overheads am I missing in calculation?


We have case opened with Microsoft for over a week now since this table needs to be replicated in prod environemnt in a week from now but they are unable to provide any help on this matter so far. I hope to get some answer here. Please let me know if I am not clear in my question.

Thanks,
Aashini Shah
Aashini Shah
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 231
Simplifying the post hoping to get answer:

We have a 3rd party vendor table with following definition: 7 non-sparse columns and 340 varchar(max) sparse columns:

CREATE TABLE [dbo].[ReportSparseData](
[ReportSparseDataId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ReportMainId] [int] NOT NULL,
[ReportYearId] [char](2) NOT NULL,
[UserId] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[DateLstMod] [datetime] NOT NULL,
[ts] [timestamp] NOT NULL,
[C1] [varchar](max) SPARSE NULL,
[C2] [varchar](max) SPARSE NULL,
[C3] [varchar](max) SPARSE NULL,
[C4] [varchar](max) SPARSE NULL,
[C5] [varchar](max) SPARSE NULL,
[C6] [varchar](max) SPARSE NULL,
--

--

--
[C339] [varchar](max) SPARSE NULL,
[C340] [varchar](max) SPARSE NULL,
CONSTRAINT [Pk_ReportSparseData_[ReportSparseDataId] PRIMARY KEY CLUSTERED
(
[ReportSparseDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-------------------------------------------------------------------
Question 1: What would be the row size for this table definition?
-------------------------------------------------------------------
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89469 Visits: 45284
Varchar(max) Sparse???
That's an incredibly weird definition.

LOB columns take a 16 byte overhead unless null (that's normal, don't need sparse for that). If they're defined sparse but are populated, they take more than that.

Personally I'd suggest removing the SPARSE attribute, that should drop the max row size to somewhere roughly around 5500 bytes (excluding header) assuming all the LOB is out of row.

You won't have row overflow with that. Overflow is for non-MAX variable length data types.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rgarrett 82118
rgarrett 82118
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 73
I recognize this as a CampusVue table.

Did you figure out a workaround for this issue?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search