varchar(max) MS SQL Server 2000, problems?

sql sql-server varchar
bplus picture bplus · Apr 10, 2009 · Viewed 31k times · Source

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!

Answer

JoshBerke picture JoshBerke · Apr 10, 2009

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.

Related questions

SQL Server Convert Varchar to Datetime

I have this date format: 2011-09-28 18:01:00 (in varchar), and I want to convert it to datetime changing to this format 28-09-2011 18:01:00. How can I do it?

Read More
Changing the maximum length of a varchar column?

I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement which is what I believe I need to …

Read More
How to run a more than 8000 characters SQL statement from a variable?

I can use the following code for tiny little queries: DECLARE @sql VARCHAR(8000) SET @sql = 'SELECT * FROM myTable' Exec @sql The above method is very useful in order to maintain large amounts of code, especially when we need to make …

Read More