SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting TEXT columns to VARCHAR(MAX)


Converting TEXT columns to VARCHAR(MAX)

Author
Message
Henk Schreij
Henk Schreij
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 832
I have a database designed in SqlServer 7 which uses text columns for the storage of text (size from 0 to ca 15000 characters). Now the text data type will be removed in a future version of Microsoft SQL Server I want to modify the text columns to the varchar(max) data type.
It is a database without stored procedures or user defined functions. All updates/inserts/deletes are done from a Windows application connected to the database using ADO.
I want to do the conversion in SSMS in the design mode, just by replacing 'text' by 'varchar(max)'. There are only a few tables with Text columns and these tables only contain a few thousend records.

Are there any problems to expect and what can I do to avoid them?



WO
WO
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 35
Hello Henk,

I would say that you need to make sure that:
1: The data remains unchanged after changing the data types
2: The client application keeps working with the modified database.

As for the first point: Since you indicate that it concerns a limited part of the database, i would make a copy of that part of the database, make the necessary changes in the copy and then compare the data in the original database and the copy, either by making a dump of the table data or use a database comparison tool.

I don't know if it is possible on text fields, but you might also want to check if there are indexes defined on the columns (not likely i think, but better safe than sorry). If there are i would rebuild them after the change of data type.

As for the second point, text columns are accessed in a different way then varchar(max) columns on pre-SQL2005 environments, this might provide a problem in the client application. The functions used to access Text columns will also be deprecated in a later version of SQL Server. Also see this topic on another forum about this subject.

Willem

Willem


Henk Schreij
Henk Schreij
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 832
Willem,

Thank you for your answer.
Ad 1.
I tried to change Text -> Varchar(max) in a copy of the database and found out the data are unchanged. So far so good.
I have no indexes on the Text field, so there is no rebuilding of indexes is required.
Ad 2.
The client application reads and writes the Text field in a normal way (using AsString, not AsMemo or AsBlob) so I don't expect any problems.
But I'll try the application in a test environment, to check for any problems.
Thank you for your suggestions. :-)



Jason Akin
Jason Akin
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 87
Hi Hank, for the benefit of others facing the same challenge, did you end up with any "gotchas" with the conversion of your app?
Henk Schreij
Henk Schreij
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 832
Hi Jason,

No answer from me Sad
All tests went fine.
But the DBA refuses to do the changes, as long there is no really need for it. Unsure
Sorry.



Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5004 Visits: 5478
...
But the DBA refuses to do the changes, as long there is no really need for it...


He follows "If it ain't broke, don't fix it" paradigm.
So, do break something to make him worry! Hehe

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Jason Akin
Jason Akin
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 87
Thank you for the update. My preliminary tests look good. The actual table change:
ALTER TABLE myTable ALTER COLUMN theCol varchar(max)

seems to affect metadata only and was almost instantaneous. My particular application had all access reading and writing as text not blob, so it's almost a non-event for me.
SQLnbe
SQLnbe
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 67
Would converting TEXT datetype columns to Varchar(max) save me some space? I have a 40GB table that has 3 Text Columns for notes and some other data that is stuffed in there with 10M records. thanks –
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search