Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

varchar(max) MS SQL Server 2000, problems?

I've inherited a asp.net website project that currently runs SQL Server 2000 as its backend.

I've been doing some databases changes on a local copy of the db using SQL Server 2005 Express. I've create a table using varchar(max) columns. They are used to stored snippets of XHTML that are of arbitrary length.

While browsing around on stackoverflow I came across this: Are there any disadvantages to always using nvarchar(MAX)?

User mattruma says he found out the "hard way" about using varchar(max) on SQL Server 2000.

What should I use instead of varchar(max) given that the live database runs on SQL Server 2000?

Thanks in advance for any help!

like image 401
bplus Avatar asked Apr 10 '09 14:04

bplus


People also ask

Is it bad practice to use varchar Max?

It is a poor practice to use nvarchar(max) or varchar(max) unles you expect to have data that needs it. Its not always a performance hit. Table scans will speed up if the row size decreases. If the varchar(max) in question is rarely used in queries, moving it out of row will be a performance gain.

What is the limit of varchar Max in SQL Server?

varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).

How can I increase varchar max size in SQL Server?

Here is how to increase field length in MySQL. Let us say you have a VARCHAR column with length 20, and want to increase its length to 255. In this case, you need to use ALTER TABLE statement to increase column size. ALTER TABLE table_name MODIFY column_name varchar(new_length);


2 Answers

VARCHAR(Max) was introduced in SQL Server 2005, and will not work on SQL Server 2000. You need to use either VARCHAR(8000) assuming that will be big enough. Otherwise you will need to use TEXT

Edit

Also if you switch to VARCHAR(8000) keep in mind there is a limit that a single row cannot have more then 8060 bytes. So if you fill up a varchar(8000) table and have a bunch of other large columns you will get an error. This is where Text comes in.

Text has performance implication because by default it is stored in a separate location, and they keep a pointer in a table. There is a set option which changes this behavior so that text types are kept in the table until they reach a certain size. If you have mostly small blobs you might want to enable this.

like image 113
JoshBerke Avatar answered Sep 28 '22 00:09

JoshBerke


It sounds like the varchar(MAX) limitations are a moot point if your live DB is SQL Server 2000, which doesn't support them. If you have more than 8K characters to store you are pretty much left with the only other option, a TEXT column. However, beware that TEXT columns have a lot of limitations too.

For example you can't sort or group on them easily, nor can you compare them for equivalency with other columns. That is you can't say Select * from mytable where Mytext1 = mytext2.

Other relevant concerns:

  • I'd suggest using an NText or NVarchar column regardless of the way you go to support Unicode.
  • If the table has a lot of other columns and the varchar(8000) column is likely to be frequently close to full, you may have problems with the row limit of 8K. Keep this in mind too.
like image 45
JohnFx Avatar answered Sep 28 '22 01:09

JohnFx