Welcome to ciysys blog

Dynamic query

Published on: 15th Oct 2013

Updated on: 19th May 2021

Overview

Dynamic query is the query that you composed at runtime and it is not known to the SQL server until you execute it. Usually, the dynamic query is stored in a variable and then passes this variable to sp_executesql (i.e., a system stored procedure) for execution.

How does it work

sp_executesql compiles the query at runtime, creates the execution plan and then executes it.

For example, the following query returns all the object ID-s in the database.

declare @sql nvarchar(max)

set @sql = '
    declare @tb table ( id int )

    insert into @tb (id)
    select object_id from sys.objects

    select * from @tb
'
exec sp_executesql @sql

You may also create a parameterized dynamic query that will look like this,

declare
    @sql nvarchar(max)
    , @sql_param nvarchar(max)

set @sql = '
    select object_id
    from sys.objects
    where object_id < @id
'

set @sql_param = '@id int'

exec sp_executesql
    @sql
    , @sql_param
    , 20

Advantage of parameterized dynamic query

Disadvantage of using dynamic query

Provides advanced feature for your application

We are using dynamic query mainly in the following area,

With the help of dynamic query, you can develop a more powerful application.

What are the most common user requirements for the report?

With dynamic query, you will be able to compose the dynamic query that meets all the above requirements.

What is the catch of using dynamic query

Dynamic query seems like able to help in fulfilling the user requirements but it also causes an issue: slow query performance.

You need lots of data to test your query, fine tuning the query and a better index.

From our experience, we were facing a slow performance issue. It was difficult to identify what caused it because the query most of the time was fast and sometimes was slow. After extensive testing and research, we found out that parameter sniffing was the cause. The query hint below solved the instability performance,

select *
from tb_sales
where
    customer = @customer
option (optimize for unknown)   --<<==== try this

Note: this query hint will not solve all performance issues. In case you are facing any strange performance issue, you will have to first find out the cause and then look for the appropriate solution.

In case you want to find out more about this, you will have to search this topic on the Internet: "parameter sniffing".

Related posts

Jump to #MSSQL blog

Author

Lau Hon Wan - the developer and business owner.