1

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?

Improve this question
1
  • Are persisted columns or materialized view available options?
    – ypercubeᵀᴹ
    Commented May 7, 2014 at 8:48

1 Answer 1

Reset to default
0

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

Improve this answer
6
  • Thanks for that I will investigate if this is possible. As for materialized view, or indexed view in SQL server, this would not be allowed as ntext is not allowed to be indexed.
    – Tom
    Commented May 7, 2014 at 11:33
  • @Tom I was thinking along the lines of CREATE VIEW x AS (SELECT ---, CAST(ntext_column AS NVARCHAR(MAX)) AS ntext_column FROM <table>; so all columns are the same as the original table except for the ntext one which is replaced. Perhaps it's easier for your app to be directed to use the view instead of the table for SELECT (but keep inserts/deletes/updates to the table.)
    – ypercubeᵀᴹ
    Commented May 7, 2014 at 11:50
  • 1
    You may not be able to use an indexed (materialized) view with tables in a Dynamics GP database. GP, at least the versions I'm familiar with, use an ODBC connection with ANSI quoted identifiers and ANSI nulls set to off. I suspect that the GP tables will not meet the requirement "The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view." See: technet.microsoft.com/en-us/library/ms191432(v=sql.105).aspx
    – Jeremy
    Commented May 7, 2014 at 13:28
  • @Jeremy can't the connection that Dynamics use have the setting to ANSI nulls off while the view was created with the ansi null setting on?
    – ypercubeᵀᴹ
    Commented May 7, 2014 at 13:34
  • @ypercube: The ANSI NULLS must be set to ON when working with any columns which are part of the index. To quote technet: "SET ANSI_NULLS also must be ON when creating or manipulating indexes on computed columns or indexed views" technet.microsoft.com/en-us/library/aa259229(v=sql.80).aspx
    – Jeremy
    Commented May 7, 2014 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.