Prev: SQL server book - 2005 or 2008?
Next: SQL Server 2005 and 2008
From: asrrjs on 22 Apr 2010 10:58
What is the best practice for converting ntext columns to nvarchar(max) in a
existing SQL 2005 or 2008 database? Is ALTER enough? Or are there other
considerations. I've read in one post that the converted columns should be
set to itself to force a reallocation of the data.

UPDATE Table1
SET TextCol1=TextCol1

Thanks.
From: Erland Sommarskog on 22 Apr 2010 17:43
asrrjs (asrrjs(a)discussions.microsoft.com) writes:
> What is the best practice for converting ntext columns to nvarchar(max)
> in a existing SQL 2005 or 2008 database? Is ALTER enough? Or are there
> other considerations. I've read in one post that the converted columns
> should be set to itself to force a reallocation of the data.
>
> UPDATE Table1
> SET TextCol1=TextCol1

You posted this question a few days back as well. I repeat my answer:

The ALTER operation seems to be only a metadata operation. I don't see
why you would need an UPDATE. True, there are different defaults for
"text in row" for ntext and nvarchar(MAX), but I am not sure that I
see this as a compelling reason to shuffle the data around.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SQL server book - 2005 or 2008?
Next: SQL Server 2005 and 2008