Advertisement
Advertisement


Which datatype and size for text with n characters in SQL Server?


Question

I'm having trouble understanding how to define a column for my text that has the right size for my max. number of characters. In Oracle I can create a VARCHAR2(10 CHAR) which will be big enough for 10 characters. The size depends on the encoding used in the database. But how do I do that in SQL Server? Do I use varchar(10)? nvarchar(10)? I want to be able to store all kinds of characters (even chinese).

2016/02/29
1
1
2/29/2016 1:45:20 PM

Accepted Answer

If you want Chinese characters, you need to use nvarchar(n) and specify a length of n that makes sense.

Those are characters you're defining, and the space you need is twice that number (since any Unicode character in SQL Server always uses 2 bytes).

Max. size is nvarchar(4000) - or if you really need more, use nvarchar(max) (for up to 1 billion characters).

I would recommend NOT to just use nvarchar(max) for everything, out of lazyness about considering what size you really need! Since it's a really large column, you won't be able to index it for one.

2016/02/29
2
2/29/2016 1:48:32 PM

If you use nvarchar(max) this will allow for any number of characters for all character sets. The system will optimise storage.

Limitations on row size are addressed here. See answer from @marc_s for limitations on the use of max.

2016/02/29