SQL Server Table Row Length Limit

Question

SQL Server Table Row Length Limit

Dave L. Clark I

SSC Veteran

Points: 256
More actions
December 28, 2005 at 4:43 pm

#92537

I understand about the limitation on SQL Server table row lengths. What I don't completely understand is how it comes up with 8,685 as the total row length when I can add up the column widths in the following SQL statement and come up with only about 4,354. Even allowing for 4 bytes of overhead per column, that only increases the total to 4,506. So, what else do I not know about?
[sql]
Create Table [System_MainTable] (
    [SystemMainKey] INTEGER IDENTITY(1,1) NOT NULL,
    [MachineName]  NVARCHAR(50) NULL,
    [PhysicalMemory]  INTEGER  NULL,
    [MemoryCapacity]  INTEGER  NULL,
    [RAMSlots]   INTEGER  NULL,
    [ChassisType]  NVARCHAR(50) NULL,
    [CPUType]   NVARCHAR(255) NULL,
    [CPUTypeExtended]  NVARCHAR(255) NULL,
    [MHz]   INTEGER  NULL,
    [CPUsLogical]  INTEGER  NULL,
    [CPUsPhysical]  INTEGER  NULL,
    [VideoAdapter]  NVARCHAR(255) NULL,
    [VideoMemorySize]  INTEGER  NULL,
    [VideoResolution]  NVARCHAR(50) NULL,
    [VideoRefresh]  SMALLINT NULL,
    [AudioDevice]  NVARCHAR(255) NULL,
    [MonitorMfg]  NVARCHAR(255) NULL,
    [MonitorModel]  NVARCHAR(255) NULL,
    [MonitorType]  NVARCHAR(50) NULL,
    [MonitorRatio]  NVARCHAR(50) NULL,
    [MonitorMaximumResolution] NVARCHAR(50) NULL,
    [MonitorSerialNumber] NVARCHAR(50) NULL,
    [MonitorManufactureDate] SMALLDATETIME NULL,
    [MonitorSize]  INTEGER  NULL,
    [ComputerType]  NVARCHAR(255) NULL,
    [SerialNumber]  NVARCHAR(255) NULL,
    [UUID]   NVARCHAR(255) NULL,
    [BIOSDescription]  NVARCHAR(255) NULL,
    [BIOSDate]   SMALLDATETIME NULL,
    [SystemMfg]   NVARCHAR(255) NULL,
    [SystemModel]  NVARCHAR(255) NULL,
    [MotherboardMfg]  NVARCHAR(255) NULL,
    [MotherboardModel]  NVARCHAR(255) NULL,
    [ScanStatus]  NVARCHAR(255) NULL,
    [LastScanned]  DATETIME NULL,
    [LastScannedDuration] NVARCHAR(50) NULL,
    [LastScannedSuccess] DATETIME NULL,
    [LastScannedSuccessDuration] NVARCHAR(50) NULL,
    CONSTRAINT [System_MainTable_Primary_Key] PRIMARY KEY ([SystemMainKey] ASC),
    CONSTRAINT [System_MainTable_MachineName_Key] UNIQUE ([MachineName] ASC)
);
[/sql]
[result]
Warning: The table 'System_MainTable' has been created but its maximum row size (8685) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[/result]
Thanks,
— Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave L. Clark I SSC Veteran Points: 256 More actions · Answer 1

Oh, I had a thought... I didn't take into consideration the Unicode values which take up 2 bytes each. Anything else, though?

Thanks,

— Dave

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Not that I can think off ATM, but that should be plenty since you have a lot of nvarchar columns in that table.

You can use the syscolumns table to see the size required for each of those columns and get a sum.

dougjjj SSCrazy Points: 2081 More actions · Answer 3

It's because you're using NVARCHAR, an NVARCHAR(50) actually consumes 100 bytes rather than 50 which is why you're topping the 8060 limit.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Given that much NVARCHAR(50) columns it looks llike a ported Access database. Do you really need

- Unicode

- Columns of that width?

To me it seems that many columns can be narrowed down.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dave L. Clark I SSC Veteran Points: 256 More actions · Answer 5

I've asked my client the same questions. For now, I've changed all NVARCHAR to VARCHAR. Yes, my client is using an Access database and I was just merily going along, while converting to an SQL Server database, and making sure that everything matched between the two databases. Until my client answers, I'm hoping the only reason they were using Unicode is because that is all Access offers. Otherwise, I will have to split the table.

Thanks,

— Dave