Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
elliottbenzle
Asked:
elliottbenzle
1 Solution
 
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
 
aryefCommented:
according to me experience, varchar(max) stores 4000 characters, while nvarchar(max) stores 2000 unicode characters.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now