Sep 22, 2013

The row size limit of 8060 bytes for memory optimized tables has been exceeded. Please simplify the table definition

In sql server 2014, if we will try to create a table by following script:

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL,
     vcName VARCHAR(200),
     vcEmailAdress VARCHAR(200),
     vcPhotoPath VARCHAR(200),
     vcAddress VARCHAR(1500),
     vcDetial VARCHAR(6000),
     moSalary MONEY,
     CONSTRAINT PK_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)
WITH(MEMORY_OPTIMIZED =ON)

We may get error message something like:

Msg 41307, Level 16, State 1, Line 1
The row size limit of 8060 bytes for memory optimized tables has been exceeded. Please simplify the table definition.

Cause: Sql server 2014 has limitation that we cannot create memory optimized tables in which some of the size of column is more than 8060 bytes. In this example it is: 8 + 200 + 200 + 200 + 1500 + 6000 + 8 = 8116.  
This is more than 8060 bytes.

Solution: We will have to decrease the sum of size of all columns up to 8060. For example:

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL,
     vcName VARCHAR(200),
     vcEmailAdress VARCHAR(200),
     vcPhotoPath VARCHAR(200),
     vcAddress VARCHAR(1500),
     vcDetial VARCHAR(5000),
     moSalary MONEY,
     CONSTRAINT PK_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)
WITH(MEMORY_OPTIMIZED =ON)

Or we can split the data into two tables. That is we will have to create two tables. For example:

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL,
     vcName VARCHAR(200),
     vcEmailAdress VARCHAR(200),
     vcPhotoPath VARCHAR(200),
     vcAddress VARCHAR(1500),
     moSalary MONEY,
     CONSTRAINT PK_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)
WITH(MEMORY_OPTIMIZED =ON)


GO

1 comment:

  1. Anonymous10/25/2013

    it should be total columns size, shouldn't exceed 8060

    ReplyDelete