Ozzie.eu

The guys from SQLServerCentral.com published an article giving a Quick Tour of sp_ExecuteSQL. This is all very well, except for the antibodies in me regarding dynamic TSQL inside frequently used stored procedures.

Yes, it’s true that “Every once in a while you find yourself working with SQL Server and you need to execute a piece of T-SQL that will be  different at the time of execution from the time that the code is being written.” but pointing out SQL injection as the single concern is quite an understatement.

The key downside events regarding dynamic TSQL are statement compile time and execution plans. You can read on the official documentation at MSDN: “The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.”

Furthermore “sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.”

One can try to optimize this dynamic execution by using parameter substitution, so the the Transact-SQL string is built only one time. But there are still aspects you can’t control:

• What is the generated execution plan for the dynamic statement, running separate from the execution plan of the batch that called sp_executesql;


• Is the execution plan any good? You can’t really tell to try to change it;


• Good or bad, the plan is cached for the following executions. If the diversity of queries that can dynamically be run is large, there’s no gain from the plan caching;


• Using the RECOMPILE option solves the above issue but creates another: each time the stored procedure is called, the execution plan is recompiled. For long running queries this might be overlooked, but for queries otherwise fast, it’s a major performance drawback.

Having stated my displeasure for the dynamic TSQL, I can also recommend the following readings:

• The Curse and Blessings of Dynamic SQL


• Is Dynamic TSQL a Good or Bad Idea?

I apologize for the rant but in all applications I witnessed the development, using dynamic TSQL inside stored procedures caused nothing but trouble as soon as the tiny tables turned into a larger, business critical database with hundreds of users.

Applications and data access development have come a long way and curiously now is the circle closes.
In the early years an application’s code was merely a reflection of the data it was meant to store and retrieve. For instance, a COBOL commarea from a program meant to access data on IBM IMS database had the structure that mapped to the data, on it’s various possibilities. Alongside with the CRUD methods implementation.

If you wanted to search through the data, you had to develop the code for it. That was achievable to the developers but consequently no Ad-hoc queries were possible for the end-users. Business intelligence was nothing but some pen and pencil with big paper stacks.
Then came relational databases. The Relational model, first proposed in 1970 by Edgar F. Codd, departed from this tradition by insisting that applications should search for data by content, rather than by following links. This was considered necessary to allow the content of the database to evolve without constant rewriting of applications.

Relational systems placed heavy demands on processing resources, and it was not until the mid 1980s that computing hardware became powerful enough to allow them to be widely deployed. By the early 1990s, however, relational systems were dominant for all large-scale data processing applications, and they remain dominant today.
The dominant database language is the standard SQL for the Relational model, which has influenced database languages also for other data models.
Finally to make my point about what this post is about, comes the trend of handling large data stores with NoSQL. It does not use SQL as its query language. NoSQL database systems rose alongside major Internet companies, such as Google, Amazon, and Facebook, which had significantly different challenges in dealing with huge quantities of data that the traditional RDBMS solutions could not cope with.

NoSQL database systems are developed to manage large volumes of data that do not necessarily follow a fixed schema. Data is partitioned among different machines (for performance reasons and size limitations) so JOIN operations are not usable and it differs from the early days IMS hierarchical database because ACID guarantees are not given.
Nevertheless, we’ve gone back to key/object systems with little flexibility, highly optimized for retrieve and append operations and often offer little functionality beyond record storage.
I state again that data development has gone full circle.