Optimize SQL Server ntext data

Optimize SQL Server ntext data

Here's a quick little tip from SQL Server 2000 Books Online that can really improve your performance. If your table contains one or more columns of ntext or text datatype, you'll want to consider running the system stored procedure sp_tableoption.


Here's a quick little tip from SQL Server 2000 Books Online
that can really improve your performance. If your table
contains one or more columns of ntext or text datatype,
you'll want to consider running the system stored procedure
sp_tableoption.

By default, ntext or text datatype data is not stored
with the rest of your row's data in the table because that
data can exceed well over a gigabtye. Instead, ntext or
text data has a pointer stored in the row to indentify the
data page where the actual data is stored.

The extra overhead hurts queries that return these column
types at a minimum. The example below sets the maximum
value to be stored in row to 7000 bytes. If the value exceeds
7000 bytes, then SQL Server will automatically store only
the pointer in row and the actual data in a separate page.
If you set the option value to 'on' instead of 7000, it uses
the default of 256 bytes.

This settings change cannot be initiated via Enterprise
Manager. Thus, many developers don't realize this option
is available. With this in mind, you'll have to actually
execute it by hand in Query Analyzer.

EXEC sp_tableoption 'mytablename', 'text in row', '7000'

As a side note, it is important to note that SQL Server
will not automatically move your data from the separate
page(s) to in row. It will make the adjustment the
next time the data is updated.

I've included a code sample that you can run in Query
Analyzer after you've executed sp_tableoption. It simply
appends a blank space to the end of the ntext or text
data. This will force SQL Server to move the data now.


DECLARE @MyText nvarchar(4000)
DECLARE @MyID int
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select MyText,MyID From MyTable

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @MyText,@MyID

WHILE @@FETCH_STATUS = 0
BEGIN
update MyTable set MyText = @MyText where MyID=@MyID
FETCH NEXT FROM @MyCursor
INTO @MyText,@MyID
END

CLOSE @MyCursor
DEALLOCATE @MyCursor






Submission Date:  9/23/2005 2:59:30 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com