The most important question to answer when dealing with Dynamic SQL is:
Answering that question correctly requires a good understanding of how SQL Server’s Optimizer works.
Dynamic SQL gives you the ability to be precise, often in situations where the application code is asking too much from the database. This is often referred to as:
- One-Size-Fits-All queries
- Catch-All queries
Why does being precise matter?
I like to refer to this as the What’s For Dinner? problem. A wife asks her husband what’s for dinner, so the husband decides to respond with questions to see what’s she in the mood for. However, they are already in the car, and she only gives him vague responses such as “I don’t want Mexican food”, or “I really want a margarita, and maybe a steak”. He ends up driving all over the town, circling back and forth several times, wasting time and gas. If she had been precise in her request, he could’ve driven straight there, and minimized the amount of time and gas spent trying to procure dinner.
The husband is the Optimizer. The wife is the query. The husband wants to answer the question the best possible way, but the wife is making it incredibly difficult. If you needed any further proof that I think like a dude, there it is. SQL Server wants to answer your question (query) quickly, but if you don’t know what you want, you really should solve that problem first.