1

There is a table with TEXT or NTEXT data type column and I need to improve the performance of queries with WHERE clause filtering out NULL or NOT NULL rows in this column.

Unfortunately, I cannot change the data type to NVARCHAR(MAX) nor add a computed column to the table.

What possibilities do I have, please?

3
  • 3
    given that the TEXT datatype has been deprecated since SQL Server 2008 I think you might be out on thin ice. What about creating an indexed view and adding the computed column there ? Feb 3 at 8:59
  • I am aware of the deprecation but cannot do anything about it. I can create and indexed view and join it to the base table a then filter base on that computed column. Do you think it would help?
    – jericzech
    Feb 3 at 9:16
  • @Mo64 Deprecated since 2005
    – SMor
    Feb 3 at 19:22
2

If you're not doing any specific search predicates in your WHERE clauses, rather you're literally trying to SELECT rows WHERE TextColumn IS NULL in one query, and WHERE TextColumn IS NOT NULL in another query, then you should look into Filtered Indexes.

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

You can create a Filtered Index for each case I mentioned above, so that subset of rows is indexed separately from each other, which may help with your performance.

2
  • I don't like filtered indexes for their unpredictability (erikdarlingdata.com/sql-server/… )...but this could help here. Thanks.
    – jericzech
    Feb 3 at 14:51
  • @jericzech Yea, inherently they're not unpredictable, rather just majorly constrained, i.e. logical equivalent predicates don't all get picked up by the same index, the predicate has to match exactly the index definition in order for it to be applicable, so certainly can be annoying especially with complex predicates. But yea in this case, it seems like you have two simple predicates where you shouldn't run into those constraints. I've used them a little in the past with much success, typically with simple predicates.
    – J.D.
    Feb 3 at 15:10
0

you can take a look at full text search

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15

A full-text index includes one or more character-based columns in a table. These columns >can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, >image, xml, or varbinary(max) and FILESTREAM. Each full-text index indexes one or more >columns from the table, and each column can use a specific language.

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.