NVarchar(MAX) for short strings

views:

1629

answers:

+2 Q:

NVarchar(MAX) for short strings

Hello,

I am interested is NVarchar(MAX) a good data type is I want to store short unicode strings which are 1-50 characters long, but most of them (more than 90%) are 5-10 characters long?

The column will not be used in comparison and ordering queries. It might be added to index as included column. Expected rows count - more than 10M.

Any recommendations? Thank you in advance!

+5 A:

Why would you use nvarchar(MAX) for such short strings? If you know the maximum length is 50 characters, make it nvarchar(50).

Also, I do not believe an nvarchar(MAX) column can be included in an index as it would break the 900 byte limit.

NYSystemsAnalyst 2009-08-04 18:55:56

I am considering NVARCHAR(MAX) because most of the time NVARCHAR(50) would be waste of space if most of the strings are 5-10 characters long. Am I right?

2009-08-04 18:57:56

I think you are misunderstanding the MAX length specifier. This does not instruct the DB to only store the actual length of the string. Instead, it tells the DB that you MAY store up to the maximum number of characters allowed (2 Gig I believe). The nvarchar (and varchar) datatypes by nature only store the actual string length of the data in that field. nvarchar(50) simply indicates that you may store data UP to 50 characters, but no more. However, if you store less than 50, that will not waste any space.

NYSystemsAnalyst 2009-08-04 19:01:32

No. You only use the space that is required. Even though it is NVARCHAR(50), if only 5 characters are used, then the storage requirement is 10 bytes. (NVARCHAR takes 2 bytes per character.)

beach 2009-08-04 19:02:33

Thank you. I got the point.

2009-08-04 19:04:19

+1 A:

I would suggest that nvarchar(50) is more appropriate if the maximum length is indeed 50.

beach 2009-08-04 18:56:56

+1 A:

NVARCHAR(MAX) (which I believe is the same as NTEXT in the earlier versions of SQL server) is intended for storing long text values of variable and potentially unlimited length.

If you know the values won't exceed N characters, you should use NVARCHAR(N).

azheglov 2009-08-04 19:05:27

ansaurus

tags:

views:

answers:

NVarchar(MAX) for short strings

related questions