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?

  • Are persisted columns or materialized view available options? – ypercubeᵀᴹ May 7 '14 at 8:48
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

  • 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 May 7 '14 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ᵀᴹ May 7 '14 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 May 7 '14 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ᵀᴹ May 7 '14 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 May 7 '14 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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