Sql-server – NTEXT,Indexing, and lob logical reads

blobindexindex-tuningperformancequery-performancesql server

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good to convert the column to nvarchar(max) however as this is a 3rd party app (its Microsoft Dynamics!) we are at present unable to do this.

The issues looks to be all the lob logical reads that are being performed as part of a RID lookup. Is there any way of speeding this up?

Best Answer

Are you allowed to add computed columns in the table or create a materialized view?

You could add a computed column and then index that:

ALTER TABLE <table>
   ADD ntext_as_nvarchar_max AS CAST(ntext_column AS NVARCHAR(MAX))  ;

CREATE INDEX index_name
  ON <table> (<index_columns>) INCLUDE (ntext_as_nvarchar_max) ;

The issue with this approach is that your queries will have to reference the new computed column (ntext_as_nvarchar_max) and not the original column for the index to be used.

Test at SQL-Fiddle

Related Question