• In order to keep the searches for the other columns nasty fast, you should set the table option (using sp_tableoption) to store the LOBs (description column) out of row.

    As for searches on the description, I'd recommend coming up with a keyword column because, as Scott mentioned, there's not much you're actually going to be able to do with an index on such a column.  First, indexes are limited to "only" 900 bytes and, second, I suspect you're going to end up using "mid-string" searches, which will cause a table scan, period.  On 20 million rows, that will lead to quite the duration using conventional methods.

    One possible solution for the searches on the description column would be the use of Full-Text Indexing.  Do understand that such indexing is a duplication of data that will require extra hard-disk space and, perhaps, some memory.  Considering your requirement to search on the description column, it'll be worth it.

    Also, read the following article because you're going to end up with what is known as a "Catch All Query" and doing it right is absolutely essential to both performance and security (avoidance of SQL Injection).  I consider the article (and there's a follow-up to the article I listed below, as well) to be the definitive article on the subject.

    https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
    https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

    As for indexes, you're probably going to need one per non-LOB column, especially when you hit the 20 million mark.  Again, that's more than a duplication of data and so you need to plan on disk size and memory size accordingly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)