Cannot create a row of size 8064 which is greater than the allowable row size of 8060

Question

I've have this strange problem when adding a column to an existing table.

The existing table looks like :

CREATE TABLE [BinaryAssets].[BinaryAssets](
 [BinaryAssetId] [int] IDENTITY(1,1) NOT NULL,
 [BinaryAssetStructureId] [int] NOT NULL,
 [Name] [nvarchar](max) NOT NULL,
 [Created_By] [int] NOT NULL,
 [Created_On] [bigint] NOT NULL,
 [Modified_By] [int] NOT NULL,
 [Modified_On] [bigint] NOT NULL,
 [Active] [bit] NOT NULL,
 CONSTRAINT [PK_BinaryAsset] PRIMARY KEY NONCLUSTERED 
(
 [BinaryAssetId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now, the sql I'm trying to execute looks like :

ALTER TABLE BinaryAssets.BinaryAssets ADD
 [Version] INT NOT NULL CONSTRAINT DF_BinaryAssets_Version DEFAULT 1

ALTER TABLE BinaryAssets.BinaryAssets
 DROP CONSTRAINT DF_BinaryAssets_Version

When I'm trying to execute I get a sqlexception (see Title).

Now, I don't think my table exceeds 8060, so what's the problem here. Strange thing is that when I change for instance the Name from nvarchar(max) to nvarchar(100), then execute my new sql and then change back the 100 to MAX, it does work... logic seems far away here.

Can anybody tell me what I'm doing wrong here?

Answer 1

The biggest size you can give an nvarchar field is of MAX, wich is 4000 chars (2 bytes Unicode chars).

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.

If you need to store a longer body of text, you should be using either text or ntext, which can hold as much text as your system has hard drive space.

You seem to be trying to create a row with a size larger than the possible size, which is not a valid operation.

Answer 2

Set the sp_tableoption stored procedure 'large value types out of row' option to ON to store the field off page.

Answer 3

In SQL Server 2005/2008, the page size is the same (8K), but the database uses pointers on the row in the page to point to other pages that contain larger fields. This allows 2005 to overcome the 8K row size limitation.