failed because the minimum row size would be 8876.

Shambhu Rai 1,406 Reputation points
2022-05-24T15:48:31.597+00:00

Hi Expert,
I am trying to increase the datatype from nchar(200) to nchar(1000) getting below mentioned error.. is there any way to get rid of this .. varchar max is not accepting certain records so need nchar only

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,641 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,450 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-05-24T21:43:37.293+00:00

    I doubt that you need nchar(1000). Or for that matter nchar(200). Keep in mind that nchar is a fixed length string. nchar(1000) would only make sense if all your column are close to 1000 characters long. But in most string columns, the length varies considerably, so nvarchar(1000) is a better choice.

    This also helps you to get around this error. This is because an nchar(1000) value always takes up 2000 bytes, even if it's empty. Whereas an nvarchar(1000) value takes up as many bytes needed to express the value, normally two bytes per character + two bytes for the length.

    If you have more overly long nchar columns, you should change these as well.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-05-24T15:56:22.07+00:00

    You would need to set it to nvarchar(1000).

    What do you mean by "varchar max is not accepting certain records"? varchar and nvarchar are different. Did you mean nvarchar?

    0 comments
  2. Bert Zhou-msft 3,421 Reputation points
    2022-05-25T01:41:33.5+00:00

    Hi,@Shambhu Rai

    If you don't plan to fill all columns with util , you need to use nvarchar (or just varchar) instead of nchar . Because an nchar(1000) needs to reserve 2000 bytes , whether you want to use it or not.

    So if you have 1000 characters in each of these columns , it won't work no matter what data type you use . The reason is that the basic storage element in SQL Server is 8K pages .

    Usually varchar(max) can be used , but there is a performance overhead if you don't recommend it... Or change the field that produces the erro r, use nvarchar(1000).

    please refer to this link.

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments