Dynamic SQL Query Performance SQL Server

Some SQL server developers hate dynamic SQL queries. There might be below possible causes:

1. Readability of dynamic SQL queries are less compare to normal T-SQL script.

2. They might think dynamic-SQL is cause of SQL-Injection.

3. They might think dynamic T-SQL forces query recompilation.

4. They might think dynamic-TSQ degrade query performance.

Yes I agree with point 1 but point 2, 3 and 3 are partially incorrect. Dynamic SQL can be prepared and parametrized and some cases it performs much better than normal T-SQL queries. In the application code all the in line queries are example of dynamic SQL. So most of cases we will not get significant performance difference. So don’t fear with dynamic SQL.  Don’t execute dynamic SQL queries like:

EXECUTE(@vcDynamicQuery)

Instead of this use system stored procedure SYS.SP_EXECUTESQL which reduces the probability of SQL injection and parameterized the SQL queries.  Here we are going to discuss the scenario where dynamic SQL queries will perform better than T-SQL queries:

When columns and its values in the parameter is very much dynamic in nature and it is not practically possible to write using IF-ELSE. For example: Let’s assume there is a business need to get the mobile numbers of all users on the base if value of parameter and if value parameter is NULL then ignore that filter. T-SQL query would be something like this:

DECLARE @ntUserID AS BIGINT 

DECLARE @vcUserName AS VARCHAR(100) = 'User_101'

DECLARE @ntUserPoint AS SMALLINT = 70

DECLARE @ntUserTypeID AS TINYINT

SELECT vcMobileNumber

FROM tblUser

WHERE (

     ((@ntUserID IS NULL) OR (@ntUserID IS NOT NULL AND ntUserID = @ntUserID)) AND

     ((@vcUserName IS NULL) OR (@vcUserName IS NOT NULL AND vcUserName = @vcUserName)) AND

     ((@ntUserPoint IS NULL) OR (@ntUserPoint IS NOT NULL AND ntUserPoint = @ntUserPoint)) AND

     ((@ntUserTypeID IS NULL) OR (@ntUserTypeID IS NOT NULL AND ntUserTypeID = @ntUserTypeID) )

)

Total logical read: 815

Note: Let’s assume user table have below index:

CREATE INDEX NCI_vcUserName

ON tblUser (vcUserName)

INCLUDE(vcMobileNumber,ntUserPoint)

In this scenario would better to write same script using dynamic SQL:

DECLARE @ntUserID AS BIGINT 

DECLARE @vcUserName AS VARCHAR(100) = 'User_101'

DECLARE @ntUserPoint AS SMALLINT = 70

DECLARE @ntUserTypeID AS TINYINT

DECLARE @vcQueryText AS NVARCHAR(MAX)

SET @vcQueryText = N'SELECT vcMobileNumber

     FROM tblUser

     WHERE 1 = 1 '

    

IF @ntUserID IS NOT NULL

     SET @vcQueryText += ' AND ntUserID = @ntUserID'

IF @vcUserName IS NOT NULL

     SET @vcQueryText += ' AND vcUserName = @vcUserName'

    

IF @ntUserPoint IS NOT NULL

     SET @vcQueryText += ' AND ntUserPoint = @ntUserPoint'

    

IF @ntUserTypeID IS NOT NULL

     SET @vcQueryText += ' AND ntUserTypeID = @ntUserTypeID'

    

EXECUTE SP_EXECUTESQL

     @vcQueryText,

     N'@ntUserID AS BIGINT ,@vcUserName AS VARCHAR(100),@ntUserPoint AS SMALLINT ,@ntUserTypeID AS TINYINT',

     @ntUserID,

     @vcUserName,

     @ntUserPoint,

     @ntUserTypeID         

Total logical read: 3

Comparison of cost using execution plan:

It is obvious that in this example dynamic SQL in much better than normal T-SQL queries since dynamic SQL is able to use index.

Note: Dynamic SQL queries are not allowed in SQL functions so in that cast think alternative of dynamic SQL. Don’t create stored procedure instead of SQL function to use dynamic SQL.