SQL Server Performance Forum – Threads Archive
Converting Text Column to VarChar
Greetings, I searched the forum and, remarkably, did not find a reference to this topic. Surely this problem isn’t unique to me. Our challenge is that we need to convert an existing column from Text to nText; the ordinal position of the column must remain the same. Obviously, ALTER TABLE <tbl> ALTER COLUMN… does not work. The only way we’ve been successful so far is adding a new nText column to the end of the table, updating the new column with data from the pre-existing Text column, then creating a new table with the appropriate column order, then re-creating constraints & indexes, and finally re-naming the tables. I don’t know whether it matters, but the ‘Text In Row’ was NOT used. Unfortunately, this is a relatively large, active production table and we can’t afford the down time that the roundabout operation would require. Are we overlooking somethingHi Jimmy , create a view that converts the Text to nText with the appropriate ordinal position and set its name to be the same as the underlying table (rename the table to a temp name). Then, run your suggested procedure in off-peak time and get rid of the view, renaming the table back to its original name. Example:
Original table T
a int
b text
c int rename table to T_1 View:
create view T
WITH SCHEMABINDING
AS
select a, convert(ntext, b) as b, c
from T_1
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au
Thanks for your suggestion. It may be something we’ll consider. I appreciate your input. So, no way to do an in place conversion?
no, you will have to recreate the table. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au
]]>