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