SQL Server 2000 Enterprise Manager - Store nvarchar data greater than 4000 characters?

views:

answers:

SQL Server 2000 Enterprise Manager - Store nvarchar data greater than 4000 characters?

I'm using SQL Server 2000 Enterprise Manager. I'm having trouble with storing some data in a column. The column has a data type of nvarchar, with a length of 4000, which I've now learned is the max length you can have.

I need to store data in the column that is longer than 4000 characters...is there a way to increase the size? Or is there another way to store it?

+3 A:

in SQL Server 2000, the only 2 ways to increase it would be:

Use the data type VARCHAR instead of NVARCHAR (VARCHAR's limit is 8000, where NVARCHAR's is 4000) NVARCHAR is the unicode version of VARCHAR.
Use the TEXT or NTEXT datatypes. TEXT stores the value outside of your table, and can store up to 2^31 - 1 characters. NTEXT can store up to 2^30 - 1 characters.

Gabriel McAdams 2010-10-12 15:51:10

Thanks, switching it to VARCHAR should give me enough length to hold the data. Although, I'm getting a warning when I try to change the column data type: Warning: Data may be lost converting column 'MyColumn' from 'nvarchar(4000)'. Is there a way to convert it without losing the data?

Steven 2010-10-12 16:05:01

That is only a warning. The loss is a possibility depending on your data. If you know that your data does not have unicode characters, then don't worry about it. Convert it anyway.

Gabriel McAdams 2010-10-12 16:09:20

@Steven - You'd need to check that you don't have any characters that will be lost in the conversion (Maybe try a query `where CAST(yourCol AS varchar(4000)) <> YourCol` and see if it returns any results)

Martin Smith 2010-10-12 16:10:06

+3 A:

First, follow @Gabriel's advice if you can live with 8000 non-Unicode characters.

In places where older versions of our app needed to store larger text strings, we allocated multiple fields (textvar, textvar2, ...) and had standard application components to split & concatenate them where they were used. Otherwise, you can use a blob & convert as needed. Ugly but functional.

DaveE 2010-10-12 15:59:20

That's how `syscomments` dealt with that issue as well (of course it makes searching with `LIKE` more problematic as the search text could be split over a boundary)

Martin Smith 2010-10-12 16:03:28

+1, I'd go with multiple split columns before attempting to use the now deprecated TEXT or NTEXT mess.

KM 2010-10-12 16:03:49

ansaurus

tags:

views:

answers:

SQL Server 2000 Enterprise Manager - Store nvarchar data greater than 4000 characters?

related questions