in MSSQL what data type should I use for long strings

I am storing some large strings in my MS SQL server. I want to use TEXT as the data type but am told that this will become obsolete in later versions of MSSQL server so I shouldn't use it. They say I should use NVarchar , but the max characters is 4,000, I need more than 4,000 characters in my strings. Is there a data type I can use for very large strings?

Thanks
LVL 4
elliottbenzleAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
In SQL 2000, you will be forced to use TEXT if it exceeds 8000 chars.

In SQL 2005, you can use varchar(MAX).

That error message doesn't look exactly like a SQL 2000 msg when you specify MAX.  What software is that msg coming from?
0
 
valkyrie_ncCommented:
You want varchar(MAX) or nvarchar(MAX); they store up to 2G data, which should hold whatever you need to put in the fields.

hth

valkyrie_nc
0
 
elliottbenzleAuthor Commented:
I tried to enter varchar(max) but get the error:

"Setting for Length must be from 1 to 8000."

leading me to believe that the maximum number of characters is 8000, which is less than I need.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aryefCommented:
according to me experience, varchar(max) stores 4000 characters, while nvarchar(max) stores 2000 unicode characters.
0
 
valkyrie_ncCommented:
what version of SQL server are you using?  Varchar(MAX) is new to 2005, but the error you're showing seems to be 2000.
0
 
aryefCommented:
when we nneded to store more that the max value, whatever it is, we split the string into a number of nvarchar(max)  fields. In practice, 4 to 6 such fields was enough  to store whatever we needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.