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?
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.
you can take a look at full text search
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.
