Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-01-18 : 04:20:28
|
| I'm in the process of migrating from SQL2000 to SQL2008, and looking to take advantage of new features introduced in SQL2005 and SQL2008Are there any issues with changing from TEXT to VARCHAR(MAX) datatypes?We have a small number of TEXT columns, and generally speaking I would like to change them to VARCHAR(MAX)We also have some current VARCHAR(7000) columns which are actually rather too tight for some of our rows, and it would be nice to extend them. We didn't use TEXT because the string handling we needed would have been too tricky, but we currently have multiple rows in some places to get around the character limitation of VARCHAR, which we would like to "fix".My perception is:Most of the data in our TEXT columns is < 8,000 characters, and I think that VARCHAR(MAX) will be held "in the record" rather than in an associated BLOB (maybe this applies to TEXT too, I haven forgotten!) thus be better for performance.It would be nice to be able to use normal String functions on columns of longer text.Probably most important is that we have Audit triggers that do an INSERT into an ARCHIVE table (from INSERTED table in Trigger) and these don't work for SQL2000 / TEXT - we have to use INSTEAD OF triggers instead, which is far more effort to maintain - and on most tables we just don't bother.We have some deployment tools that copy data from DatabaseA to DatabaseB, but only for rows have have changed. We use comprehensive WHERE clauses to decide what has changed (D is the Alias for the destination table and S the Source table):WHERE (D.[MyCol] <> S.[MyCol] OR (D.[MyCol] IS NULL AND S.[MyCol] IS NOT NULL) OR (D.[MyCol] IS NOT NULL AND S.[MyCol] IS NULL))OR ( ... next column test ...)of course we can't just do an EQUAL test for TEXT columns, so we have to compare DATALENGTH and the first 8000 characters:OR (DATALENGTH(D.[MyCol]) <> DATALENGTH(S.[MyCol]) OR CONVERT(varchar(8000), D.[MyCol]) COLLATE Latin1_General_BIN <> CONVERT(varchar(8000), S.[MyCol]) OR (D.[MyCol] IS NULL AND S.[MyCol] IS NOT NULL) OR (D.[MyCol] IS NOT NULL AND S.[MyCol] IS NULL))which is not 100% guaranteed to find differences.Presumably I'll just be able to do EQUALS test with VARCHAR(MAX)?Are there any gotchas? and other hidden benefits?Thanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2010-01-18 : 06:06:51
|
| there are no problems changing texts to varchar(max). if you're storing file contents in the text columns you might want to consider FILSTREAM storage.varchar(max) is the same as every other varchar in regards to string functions.as for triggers you can use after triggers with service broker to make them async which would speed things up considerably.then make a diffgram in xml and store that in the table. just a thought.if you have unicode stuff then you might try waiting for 208 R2 which will also have unicode compression.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.7 out! |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-18 : 06:10:10
|
| I dont think there will be any issues from changing TEXT to VARCHAR(MAX) datatypeIt is already specified in the BOL that ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.Also MS didn't specify anything about behavioural changes due to thishttp://msdn.microsoft.com/en-us/library/ms143532.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-18 : 06:36:44
|
Thanks chaps. No FILEs stores in DB here  , so its only really slightly-larger-than-8K text that I'm trying to store.I'll look at Service Broker for my After triggers, thanks for that. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
|
|
TEXT / VARCHAR(MAX) considerations?