CODEWITHSUNDEEP
sql-servervarcharnvarchar
John
John

Reputation: 975

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

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

Upvotes: 1

Views: 979

Answers (2)

marc_s
marc_s

Reputation: 755411

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.

Upvotes: 2

Peter Smith
Peter Smith

Reputation: 5552

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.

Upvotes: 0

Related Questions