Ah, yeah, pretty sure nvarchar(MAX) was added in SQL 2005, unfortunately. So, to explain further, ntext holds only a pointer to the actual content elsewhere on the server, held in a Large Object (LOB). nvarchar(MAX) holds the first 8k of data in the table, so to speak, and then only pushes overflow data out via pointer. Since oftentimes the data in ntext fields isn't really that long (but the column has to account for those times it *is* long), by using MAX you actually save space and end up with an indexable column. The UPDATE basically pulls the first 8k of data into the table, replacing the old ntext pointer with the new MAX structure.
Pete Freitag had an entry on it: http://www.petefreitag.com/item/734.cfm He further linked to another post about why to run the additional UPDATE as well: http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx On 4/23/2011 9:56 PM, Jenny Gavin-Wear wrote: > Hi Jason, > > Many thanks for your reply, it would be great if this will work on SQL 2000. > > I tried testing this: > > ALTER TABLE testing > ALTER COLUMN testing nvarchar(MAX); > > Server: Msg 170, Level 15, State 1, Line 2 > Line 2: Incorrect syntax near 'MAX'. > > I Googled a bit and found a couple of postings from people saying that using > nvarchar(max) truncated their existing data. > > I'm a bit confused about what you say about: > >>> That lets SQL keep the first set of chars locally, and only points to >>> the LOB when it needs to (only records that really have long values in >>> myNText). > Could you explain a little more, please? > >>> nvarchar(MAX) is the new ntext, and ntext is being >>> deprecated, so it's a good move at any rate. > Thanks for the warning on this. > > Jenny > > > > -----Original Message----- > From: Jason Fisher [mailto:ja...@wanax.com] > Sent: 24 April 2011 02:18 > To: cf-talk > Subject: Re: Problem Using QueryNew > > > > Change all your ntext to nvarchar(MAX), if you can. > > And run an UPDATE to free up space after the conversion, too ;) > > ALTER TABLE myTable > ALTER COLUMN myNText nvarchar(MAX); > > UPDATE myTable > SET myNText = myNText; > > That lets SQL keep the first set of chars locally, and only points to > the LOB when it needs to (only records that really have long values in > myNText). nvarchar(MAX) is the new ntext, and ntext is being > deprecated, so it's a good move at any rate. > > > On 4/23/2011 8:29 PM, Jenny Gavin-Wear wrote: >> Hi Maureen, >> >> I found it is possible to create indexed views and add them to a fulltext >> catalog, however, among a longgg list of constraints MS say that the views >> cannot contain Ntext fields, which rules out that option for me. >> >> http://msdn.microsoft.com/en-us/library/aa933148%28v=sql.80%29.aspx >> >> -----Original Message----- >> From: Maureen [mailto:mamamaur...@gmail.com] >> Sent: 24 April 2011 00:26 >> To: cf-talk >> Subject: Re: Problem Using QueryNew >> >> >> >> Seems to me it would a lot more efficient to make a view in your >> database that only returns the two fields you want to search on plus >> any fields you need for search criteria. Or do a select with a join >> that returns the fields. Once you do that, they are already in a >> query, so you wouldn't need to make another query to hold them. >> >> On Sat, Apr 23, 2011 at 4:21 PM, Jenny Gavin-Wear >> <jenn...@fasttrackonline.co.uk> wrote: >>> Many thanks, Will. Obvious now I look at it again, duh me. >>> >>> Anyhoo, I had this (probably dumb) idea of combining sever fulltext > search >>> query results into one table, turning into a lot more work than I > expected >>> (+ learning curve). >>> >>> Maybe there is a better way of going about this: >> >> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343945 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
