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:
it should be total columns size, shouldn't exceed 8060
Post a Comment