Geek Answers Handbook

Change column type from ntext to varbinary (max)

I have a table with a field ntext. MSDN says it's ntextoutdated and offers other data types:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new designs and plan to modify applications that currently use them. Use nvarchar (max), varchar (max), and varbinary (max) instead.

In my particular case, it was decided to switch to varbinary(max). I tried to change the definition of the table, but that did not work.

ALTER TABLE MyTable ALTER COLUMN MyColumn VARBINARY(MAX);

What are the options to change the type to varbinary(max)? I tried changing the type from ntext→ nvarchar(max)and then from nvarchar(max)→ varbinary(max), but this (error: implicit conversion from nvarchar (max) to varbinary (max) data type is invalid).

The only working solution is to add a new type column varbinary(max), convert the existing value to a new column, and then delete the old column. It takes WAY TOO MUCH time (on my dataset about 15 GB it takes about 30 minutes). This is why I am exploring other possibilities to achieve the same (perhaps in place = without moving data and transforming).

+5
source share
3 answers

, varbinary (max), ntext ? , , varbinary (max) , ntext . .

" nvarchar (max) varbinary (max) " , .

+3

, - . , , varchar (max) , 20 + . , .

, , , . .

, .

0

, , . , , .

  • varbinary (max) NULL
  • , .
  • In your free time, say, overnight, run the UPDATE statement with CAST
  • Remove all code support for the old column, make sure the new column is read
  • Drop the old column and change the new column to zero if necessary
0
source

More articles:


All Articles