Menu
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.
242
Some Differences Between VARCHAR and TEXT The VAR in VARCHAR means that you can set the max size to anything between 1 and 65,535. TEXT fields have a fixed max size of 65,535 characters. A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index.
The CAST() function converts a value (of any type) into a specified datatype.
The MAX() function can be allpied on the varchar columns.
varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
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)
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
