SQL storing strings

Question

I need to saving strings in database. Each string has different size from 1 to N. I think that limit will be about 10000, maybe more. nvarchar(MAX) will be best way to resolve this ?

Answer 1

In SQL Server 2005 and later, NVARCHAR(MAX) is the best way to store strings that can be more than 4000 characters long.

It's the only way recommended for SQL Server 2005 and higher, though it still supports TEXT columns for backward compatibility.

Note, that you cannot create an index on an NVARCHAR(MAX) column.

To use the index, create a computed column and use it in index searches. It may serve as an efficient coarse filter:

CREATE TABLE foo (id INT NOT NULL, mytext NVARCHAR(MAX) NOT NULL, mytext_index AS CAST(mytext AS NVARCHAR(450))

CREATE INDEX ix_foo_mytext (mytext_index)

SELECT  *
FROM    mytext
WHERE   mytext_index LIKE 'L%'

Answer 2

nvarchar is correct if they are unicode (or other non-7-or-8-bit strings). If they are for instance ASCII latin-1 text (sufficient in many cases), varchar(MAX) will save space.