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

Question

I checked every link of stackoverflow (I Rebuild table too) and others link but no luck.

I have to analyze one application. So I I got backup file, via application utility on particular path. I restored backup file in Sqlserver 2014 full version properly. Then I change the config file of the application to this sqlserver.

After that, when run the application and submit employee form via applicaion its gives the above error.

Employee table db structure as below, it contains 47 columns and does not have any rows( This is first entry).

CREATE TABLE [dbo].[employee](
    [wcsuid] [nchar](30) NULL,
    [lastupdatedate] [nchar](8) NULL,
    [updatedatetime] [varchar](17) NULL,
    [locationid] [nchar](20) NULL,
    [isactive] [nchar](1) NULL,
    [CreatedDate] [varchar](10) NULL,
    [sdate] [nchar](90) NULL,
    [identityemployeeno] [nchar](45) NULL,
    [employeeid] [nchar](180) NULL,
    [firstname] [nchar](120) NULL,
    [lastname] [nchar](120) NULL,
    [ssn] [nchar](99) NULL,
    [dob] [nchar](90) NULL,
    [gender] [nchar](9) NULL,
    [address] [nchar](180) NULL,
    [addressLine2] [nchar](180) NULL,
    [city] [nchar](105) NULL,
    [state] [nchar](180) NULL,
    [zipcode] [nchar](135) NULL,
    [isinternationaladdress] [nchar](9) NULL,
    [telephone] [nchar](126) NULL,
    [cellphone] [nchar](126) NULL,
    [IdentityJobTitleNo] [nchar](45) NULL,
    [manager] [nchar](120) NULL,
    [hiredate] [nchar](90) NULL,
    [terminationdate] [nchar](90) NULL,
    [username] [nchar](90) NULL,
    [userpassword] [nchar](90) NULL,
    [userpassword_required] [nchar](9) NULL,
    [employeestanding] [nchar](180) NULL,
    [identityroleid] [nchar](45) NULL,
    [loggedin] [nchar](9) NULL,
    [punchedin] [nchar](9) NULL,
    [punchedinTime] [nchar](90) NULL,
    [punchedinDate] [nchar](90) NULL,
    [contactname] [nchar](120) NULL,
    [contactaddress] [nchar](180) NULL,
    [contactaddressLine2] [nchar](180) NULL,
    [contactcity] [nchar](105) NULL,
    [contactstate] [nchar](30) NULL,
    [contactzipcode] [nchar](135) NULL,
    [isinternationalcontactaddress] [nchar](9) NULL,
    [contactphone] [nchar](126) NULL,
    [contactcellphone] [nchar](126) NULL,
    [relationtoemployee] [nchar](180) NULL,
    [worksoncommission] [nchar](9) NULL,
    [overrideitemcommission] [nchar](9) NULL
) ON [PRIMARY]

And insert statement (also have 47 values) with error also show in application as below :

INSERT INTO employee
(
wcsuid,lastupdatedate,updatedatetime,locationid,isactive,CreatedDate,sdate,identityemployeeno,employeeid,firstname,lastname,ssn,dob,gender,address,addressLine2,city,state,zipcode,isinternationaladdress,telephone,cellphone,IdentityJobTitleNo,manager,hiredate,terminationdate,username,
userpassword,userpassword_required,employeestanding,identityroleid,loggedin,punchedin,punchedinTime,punchedinDate,contactname,contactaddress,contactaddressLine2,contactcity,contactstate,contactzipcode,isinternationalcontactaddress,contactphone,contactcellphone,relationtoemployee,worksoncommission,overrideitemcommission) 
VALUES 
(
'2015121512201580210002','','20151215122015802','','','20151215','','4','','Ajayendra','Raghuvanshi','','19782707','M','','','','','','','0792762063','','','','00000000','00000000','ajayendra',
'SdYHcqaxf1gMjjSkjmpUiw==','N','','','','','','','','','','','','','','','','','N','N')

Is the limit of char fields, but I checked all length with the values and does not get any logic behind error.

So question is How to solved the error and why it is comes?

I'm not a SQL Server expert, but would it be possible for you to normalize your schema and move some columns out of the above table? This would increase the maximum number of rows available for you to use. — Tim Biegeleisen, Dec 15 '15 at 08:16
A lot of those fields appear to be sized much too large (e.g. 99 chars for ssn) or of the wrong type (the date and time ones). — Andrew Morton, Dec 15 '15 at 09:35

urban · Accepted Answer · 2015-12-15T08:47:50.657

I am not an SQL Server expert but... Adding all the field length above I got 4,046 (I might have made few mistakes - it is too early here and I am still on the first coffee...). Now looking at the manual:

nchar [ ( n ) ] Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..

Note that storing each characters requires two bytes. Doing 2 * 4046 we have 8092 bytes line length! Also note that nchar is fixed length which I assume is allocated when an entry/row is created and thus it generates the error that you see, telling you that the line is too long. I would expect it to warn you at least when you create the table...

Solutions

normalize more: Split the table into two, for example have an emploeeyAddress table which can make sense if you later need home and work addresses for each employee
Use nvarchar which is variable or dynamic length. The storage is still 2 bytes per character but only for the data inserted (not pre-allocated). You will hit the same limit if you try to populate all fields to their maximum length

Thank dear, but as I told it is application database to analyze and I do not normalize or add/change db structure this stage. I will check your solution. — Ajay2707, Dec 15 '15 at 08:43
Missed that part... coffee related issues :) Any luck with `nvarchar`s? — urban, Dec 15 '15 at 09:02

score 0 · Answer 2 · answered Dec 15 '15 at 08:57

0

For nchar values, each character uses up two bytes, so you should multiply your character count by two to get the total row size.

answered Dec 15 '15 at 08:57

Boluc Papuccuoglu

2,318
1
14
24

score 0 · Answer 3 · edited May 23 '17 at 11:45

0

Finally I got the solution. I am in analyze phase, so I can not change in normalize table.

After reading this below link, I understand the issue. So I just recreated a "Employee" table with nvarchar datatype as I have only option.

Thanks @urban to give the right direction and for this I accept his answer.

What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

edited May 23 '17 at 11:45

Community

1
1

answered Dec 15 '15 at 09:57

Ajay2707

5,690
6
40
58

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

3 Answers3

Solutions