Convert between text and varchar(MAX) in SQL Server

sql-server

Solution

Yes, definitely - `VARCHAR(MAX)` is the type you should be using anyway. The underlying implementation of both types is essentially the same (on large enough data, or after a type change from text to `VARCHAR(MAX)`), if you worry about that.

You can even "convert" an existing column of type `TEXT` to `VARCHAR(MAX)` by means of:

ALTER TABLE dbo.YourTableHere
ALTER COLUMN YourTextColumnHere VARCHAR(MAX)

This will turn your `TEXT` column into a `VARCHAR(MAX)` column without any data loss.

Try it! (on a copy of your existing database first, of course)

Problem

I have read only access to some SQL Sever 2008 R2 database and I need to copy data from some of its tables to the tables in my database; both databases have the same collation. The source database uses a lot of columns of `text` datatype. Can I safely make the target columns in my database of type `varchar(MAX)` and copy data without any risk (I am using INSERT statements to copy data)? In other words, can I safely copy string data from column of `text` type to the column of `varchar(MAX)`? Both columns use the same collation.

Original source