Can I change Text field to nvarchar(max)?

This is SQL 2014

We have a table with 3 Text fields. It holds data that has been compressed in .Net code. Not encrypted, it's compressed and .Net code decompresses the data to display on the screen.

It looks like this
r1.png
I don't know if we'll lose the data but can I change Text field to nvarchar(max) without losing data?
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ScriptAddictCommented:
If it was me I'd attach to the back end database and copy the table, and then attempt an alter table command to see how it does.

It seems to me I normally get warnings if a conversion or change would cause data problems.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 - The table here seems to indicate it is an implicit conversion, so I'd assume you are safe.

Here's a good one from stack overflow that should clear up any lingering questions:

https://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server

The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHAR column can be. However, if you do have a lot of data you will have a huge performance issue using these methods.

In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.

If you are searching large amounts of text and performance is key then you should use a Full Text Index.

LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
If it was me I'd attach to the back end database and copy the table
What do you mean by "attach to the back end database"? you mean copy the table as back up?
Scott FellDeveloper & EE ModeratorCommented:
I just ran into the same situation and did the same thing. I first backed up the db. Then ran a query to find the max length of the fields that needed to be converted and based on that estimated the maximum size needed.  If I saw the actual data only took up 80 characters, I made it 150 to be safe.   The conversion for me went smooth and worked as expected.  I ended up adding a new field to the table as a text and naming it slightly different. Then changed the field type to to varchar(x). I only did this after making a backup and doing some testing.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ScriptAddictCommented:
Yup backup your database before you do it.
CamilliaAuthor Commented:
I want it "max". I can try it tomorrow at work and see.
Mark WillsTopic Advisor, Page EditorCommented:
Yes, you can change in place....
Best to change when no one is using / accessing the table...  (See Step 0 below, Through to -- YAY)

OR...  (See Step 1 onward below)

Take a copy of the table
Add a new column NVARCHAR(MAX)
Populate that from your TEXT column
Test.


Dont panic though. While it does say TEXT will be deprecated... Even in SQL Server 2017 it says
The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been determined.
See : https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017#features-deprecated-in-a-future-version-of-sql-server 

Which basically means that at some future version of SQL Server AFTER SQL 2017, it might be deprecated. That's quite a few versions away from what you currently use. They cant even advise you of what version (yet).... When they do know a version, you will have plenty of time ahead of that release before you must then act.

Now, TEXT should become VARCHAR(MAX) and NTEXT should become NVARCHAR(MAX)

Script for the above. I will use generic but descriptive names. Please read through the script before you start, making any changes you might need
-- Step 0
-- let's create a table to test with

create table tbl_with_TEXT_columns (ID int identity, FirstName varchar(20), LastName varchar(20), DOB datetime, CurriculumVitae TEXT, Dateadded datetime default getdate())
go

-- Now populate

insert tbl_with_TEXT_columns values ('A','B','1920-02-29','TextTextText',getdate())
go

-- Take a copy of the Table

select * into BAK_1_tbl_with_TEXT_columns from tbl_with_TEXT_columns
go

-- Now lets try to change the datatype

alter table BAK_1_tbl_with_TEXT_columns alter column CurriculumVitae varchar(max)
go
-- Commands completed successfully.

-- thats good news... Lets have a look at the data

select * from BAK_1_tbl_with_TEXT_columns
go

-- check column types

select C.Name,C.Column_ID,T.name,C.max_length
from sys.columns C 
inner join sys.types T on c.user_type_id = t.user_type_id
where object_name(object_id) = 'BAK_1_tbl_with_TEXT_columns'

-- compared to original

select C.Name,C.Column_ID,T.name,C.max_length
from sys.columns C 
inner join sys.types T on c.user_type_id = t.user_type_id
where object_name(object_id) = 'tbl_with_TEXT_columns'

-- so we can see that it has changed in place....
-- YAY
-- Shouldnt have to worry about the rest


-- Now to test the "Steps" way

-- Step 1  -- take a copy

select * into BAK_2_tbl_with_TEXT_columns from tbl_with_TEXT_columns
go

-- Step 2 -- add a new column of varchar(max)

Alter table BAK_2_tbl_with_TEXT_columns add VC_CurriculumVitae varchar(max)
go

-- Step 3 -- now populate the new column

update BAK_2_tbl_with_TEXT_columns set VC_CurriculumVitae = CurriculumVitae
go

-- Check the Table Structure

select C.Name,C.Column_ID,T.name,C.max_length
from sys.columns C 
inner join sys.types T on c.user_type_id = t.user_type_id
where object_name(object_id) = 'BAK_2_tbl_with_TEXT_columns'

-- TEST --- Up to you, suggest testing by swapping between VC_CurriculumVitae and CurriculumVitae for whatever you are using the table for

-- once happy with testing, you could drop the original column and rename the new column

Alter table BAK_2_tbl_with_TEXT_columns drop column CurriculumVitae
go
EXEC sp_rename 'BAK_2_tbl_with_TEXT_columns.VC_CurriculumVitae', 'CurriculumVitae', 'COLUMN'
go

-- Check the Table Structure

select C.Name,C.Column_ID,T.name,C.max_length
from sys.columns C 
inner join sys.types T on c.user_type_id = t.user_type_id
where object_name(object_id) = 'BAK_2_tbl_with_TEXT_columns'

-- ALL DONE

-- Now lets clean up

-- WARNING

-- We are about to remove the tables used in this test scenario

drop table tbl_with_TEXT_columns
drop table BAK_1_tbl_with_TEXT_columns
drop table BAK_2_tbl_with_TEXT_columns
go

Open in new window

Oh, and as for compression, have a read of : https://sqlperformance.com/2017/01/sql-performance/compression-effect-on-performance
CamilliaAuthor Commented:
Mark, thanks!
(See Step 0 below, Through to -- YAY)
That made me chuckle :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.