Sql-server – Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows

performancequery-performancesql server

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?

Best Answer

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.

Related Question