Evaluate special programming constructs
May include but is not limited to: dynamic vs. prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protecting against SQL injection
It won’t be so surprising, but I tell you that SQL Server has some great optimization features. One of these is that it caches query plans. A query plan is a compiled form of what the database engine should do. It stores cached plans in a normalized form, and performs hash lookups when it meets with the same hash to skip compiling the given query, and use an existing execution plan. Marvelous.
Sometimes you need quite a lot of flexibility – let’s say you are dealing with optimal parameters. Optimal parameters tend to result in different query execution plans, thus slower performance. This is the time when dynamic SQL comes into place.
The main difference between static and dynamic SQL is that static SQL is passed to SQL Server to deal with, while dynamic SQL is created inside SQL Server. You can create and execute dynamic SQL queries by using the EXECUTE command or the sp_executesql system stored procedure. The use of the latter is strongly courage, because it has a considerable benefit: support for parameterization. Parameterization has two great features: It provides a shield against SQL injection attacks, and parameterized queries can be cached in normalized forms by SQL Server, so there’s a huge performance benefit.
I’d not waste words on EXECUTE, because sp_executesql is so much cooler. It takes three values:
- The string of the query.
- A parameter list with name and type, as a comma-delimited list.
- The list of the parameter values, comma-delimited.
Let’s say a thing or two on SQL injection. Everybody knows SQL injection, it’s simplicity and power is amazing. You have two simple and such powerful allies: parameterized queries and a good load of suspicion against user input. And of course, the golden rule: never ever build an SQL query with string concatenation. This applies to everything: SQL Server, ADO.NET, and anywhere you’d write a SQL query.