ansaurus

Question

How to switch from VARCHAR to TEXT in SQL 2000?

Answer 1

+1 A:

Many T-SQL string functions won't work with text - they only work with char/varchar/nchar/nvarchar.

Daniel Renshaw 2010-04-30 18:27:17

Answer 2

+2 A:

Not sure if you cannot just alter the column's data type:

ALTER TABLE dbo.YourTable
  ALTER COLUMN YourColumn TEXT

Doesn't that work? (not sure if that worked back on 2000.....)

As for things to remember: the TEXT data type also is a lot more cumbersome to work with if you need to do string manipulation, since most if not all the usual string functions like LEN, SUBSTRING etc. don't work on it. So in T-SQL itself, it's a major PIA to work with TEXT columns....

But other than that, you should be fine, I believe.

marc_s 2010-04-30 18:27:30

yes, I'm pretty sure this should work to.

Chris Simpson 2010-04-30 18:29:14

Hmm, interesting. I was looking at the change script that enterprise manager created after changing the data type. I'll test that idea.

MatthewMartin 2010-04-30 18:36:21

Answer 3

+1 A:

You cannot search the fields, for one, and many queries will not work, which use string manipulation functions. Better to split the field into two or more. Seriously, if that's the only way, because an upgrade to the Express version of SQL Server 2008 would probably be better than sticking with SQL Server 2000 at this point, IMHO. Actually, splitting the fields would just be bad too. Forget that. I would not do it, unless my boss refused to upgrade, then I'd have to pick and choose which fields to VARCHAR(MAX) and which to split up, and which may get away with Text as the type.

Richard Hein 2010-04-30 18:31:55

I wish I could, varchar(max) is a much more elegant solution. But big government organizations move at their own pace and I can't hurry them.

MatthewMartin 2010-04-30 18:37:25

Too true, been there, done that. Sadly there is not one solution that works for all the fields in general. If some field is used in a stored proc, you need to know about it ... you'll have to find every instance of that field. The refactoring tools in Visual Studio for DB Pros (and whatever they are calling it in 2010), would help, but I don't know what capabilities are present if you use it against 2000. Or if you have RedGate's tools, great, you can find the ones that can't be Text. Good luck. I feel the pain.

Richard Hein 2010-04-30 18:42:10

Answer 4

+2 A:

At least:

Almost no string functions
Can't be used in AFTER triggers
Not as local variables
Hard to manipulate (eg have to use pointers)
Comparison is a pain (from devio)

Back in ye olden SQL Server 6.5 days, I stored some data in several varchar(255) columns because varchar(8000) was only introduced with SQL Server 7.

Lordy, how did I forget this? ~~It's ugly, but could you manage with a few varchar(8000) coumns concatenated?~~

gbn 2010-04-30 18:44:05

You can't actually fill multiple varchar(8000) columns because you'll hit the 8KB maximum row size. A single 8000 column will use up most of the 8KB.

MatthewMartin 2010-04-30 23:09:21

@MatthewMartin: ah, what was I thinking...? Thank you and corrected

gbn 2010-05-01 05:33:15

afaik you cannot compare and index text columns, either. the 8k record size limit does not apply to sql2005+

devio 2010-05-01 12:47:30

@devio: we are on about SQL Server 2000 says OP. Otherwise we'd not be discussing text/image

gbn 2010-05-01 12:50:33

ansaurus

tags:

views:

answers:

How to switch from VARCHAR to TEXT in SQL 2000?

related questions