Cannot create a string of size 8064 that is larger than the valid string size of 8060

Question

I had this strange problem when adding a column to an existing table.

The existing table looks like this:

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 this:

 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 try to execute, I get sqlexception (see Title).

Now, I don’t think my table exceeds 8060, so the problem is here. The strange thing is that when I change, for example, the name from nvarchar (max) to nvarchar (100), then execute my new sql, and then change back 100 to MAX, it works ... the logic seems far here.

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

Answer 1

The largest size you can specify in the nvarchar field is MAX , which is 4000 characters (2 Unicode bytes).

In SQL Server 2000 and SQL Server 7 , a string cannot exceed 8000 bytes. This means that a VARBINARY column can only store 8000 bytes (assuming that it is the only column in the 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 internal page size of 8 KB SQL Server uses to save data to disk.

If you need to save longer text, you should use text or ntext , which can contain as much text as your system has on your hard drive.

It seems you are trying to create a string with a size larger than the size that is not valid.

Answer 2

Set the sp_tableoption parameter to the 'large value types out of row' stored procedure to ON to save the field outside the page.

Answer 3

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