November 29, 2010 at 3:05 am
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?
November 29, 2010 at 4:59 pm
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
November 30, 2010 at 1:39 am
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. 🙂
May 8, 2012 at 3:02 pm
Hi Hank, for the benefit of others facing the same challenge, did you end up with any "gotchas" with the conversion of your app?
May 9, 2012 at 1:46 am
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. :unsure:
Sorry.
May 9, 2012 at 3:00 am
...
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:
May 9, 2012 at 4:59 am
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.
November 10, 2014 at 10:50 am
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 –
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply