Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting TEXT columns to VARCHAR(MAX) Expand / Collapse
Author
Message
Posted Monday, November 29, 2010 3:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 18, 2014 1:40 PM
Points: 251, Visits: 660
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?



Post #1027207
Posted Monday, November 29, 2010 4:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 27, 2012 9:00 AM
Points: 33, 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

Post #1027682
Posted Tuesday, November 30, 2010 1:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 18, 2014 1:40 PM
Points: 251, Visits: 660
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.



Post #1027796
Posted Tuesday, May 8, 2012 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:47 PM
Points: 24, Visits: 74
Hi Hank, for the benefit of others facing the same challenge, did you end up with any "gotchas" with the conversion of your app?
Post #1296775
Posted Wednesday, May 9, 2012 1:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 18, 2014 1:40 PM
Points: 251, Visits: 660
Hi Jason,

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



Post #1296942
Posted Wednesday, May 9, 2012 3:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
...
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!



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

How to post your question to get the best and quick help
Post #1296978
Posted Wednesday, May 9, 2012 4:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:47 PM
Points: 24, Visits: 74
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.
Post #1297019
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse