SQL SERVER: String or binary data would be truncated. nvarchar

Question

I'm trying to insert into the following table:

enter image description here

but for some reason I cannot insert more than 250 characters into slabel1 field, even though it's size it's 500. Every time this happens I receive the following error:

String or binary data would be truncated. The statement has been terminated.

I do not understand why.

I don't know SQL Server, but perhaps utf-8 require 2 bytes / character? — jarlh
– jarlh, Commented May 20, 2015 at 8:12
How did you obtain the information in the question? What is this length? Is it the number from when you declared the table, or is it the length in bytes? — Lasse V. Karlsen
– Lasse V. Karlsen, Commented May 20, 2015 at 8:24
@jarlh you are right that each character takes 2 bytes rather than 1 but specifying 500 for nvarchar should still allow 500 characters the cost would be 1000 bytes. Question is, is the screenshot above the table design or a result of some query? — Christian Barron
– Christian Barron, Commented May 20, 2015 at 8:28
I obtained that table is sql server 2008 by selecting the table name with mouse cursor and pressing ALT + F1. I was told that I can find each column type and length like this — user2405219
– user2405219, Commented May 20, 2015 at 8:31

Jamie Pollard · Accepted Answer · 2015-05-20 08:36:11Z

10

Your results show the length in bytes of the column, not how many characters it can store. The column you are using is an nvarchar column, and so a character will take up 2 bytes instead of 1, so in your case (500 / 2) = 250 characters max.

This will show you the difference, we have two columns, each which can hold 50 characters, but the length of the nvarchar column is 100

CREATE TABLE [#text]
(
  [Text] VARCHAR(50),
  [NText] NVARCHAR(50)
)

SELECT COL_LENGTH( 'tempdb..#Text' , 'Text' ) [Varchar_Length],
       COL_LENGTH( 'tempdb..#Text' , 'NText' ) [NVarchar_Length]


DROP TABLE [#text]

The results are:

Varchar_Length | NVarchar_Length
50             | 100

edited May 20, 2015 at 8:36

answered May 20, 2015 at 8:23

Jamie Pollard

1,5991 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kolappan N · Accepted Answer · 2017-07-20 15:54:05Z

1

Because it's NVARCHAR

nchar and nvarchar

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..

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

Try this one to see the differences

DECLARE @text1 NVARCHAR(200)
DECLARE @text2 VARCHAR(200)

SET @text1 = 'aaaaaaaa'
SET @text2 = 'aaaaaaaa'

SELECT LEN(@text1), DATALENGTH(@text1)

SELECT LEN(@text2), DATALENGTH(@text2)

edited Jul 20, 2017 at 15:54

Kolappan N

4,0413 gold badges40 silver badges45 bronze badges

answered May 20, 2015 at 8:13

sqluser

5,7027 gold badges44 silver badges52 bronze badges

1 Comment

Lasse V. Karlsen Over a year ago

nvarchar(200) means 200 characters (or Unicode UCS-2 codes), not 200 bytes. The usage of the word bytes in the documentation specifically relates to nvarchar(max). n defines the string length

Collectives™ on Stack Overflow

SQL SERVER: String or binary data would be truncated. nvarchar

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related