Dynamic ORDER BY clause in sql server

Dynamic ORDER BY clause in sql server: Query performance

Sometime we need to sort the data of a table according to parameter of a stored procedure. For example I am creating a table and inserting some records into it:

--Deleting tblEmployee table if it already present.

IF OBJECT_ID('tblEmployee') IS NOT NULL

     DROP TABLE tblEmployee

--Creating tblEmployee.

CREATE TABLE tblEmployee(

     ntEmpID BIGINT PRIMARY KEY IDENTITY,

     vcName VARCHAR(Max),

     ntAge INTEGER

)

--Create Non clustered index

CREATE NONCLUSTERED INDEX NCI_Age ON tblEmployee(ntAge)

--Inserting some records into it 

INSERT INTO tblEmployee

SELECT [Text], severity FROM Sys.messages 

Now I want to create a stored procedure, when we will pass 1 then it will sort according to ntAge column otherwise it will sort according to ntEmpID columns. It is very easy to write:

CREATE PROC uspGetEmp(

     @ntSort_Order AS INTEGER

)

AS

BEGIN

     SELECT

          ntAge, ntEmpID

     FROM tblEmployee

     ORDER BY

          CASE

              WHEN @ntSort_Order = 1 THEN ntAge

               ELSE  ntEmpID

          END

END

  

To execute:

EXECUTE uspGetEmp 1

Or

EXECUTE uspGetEmp 2

It works fine.

Let's check its plan how this sorting is performed dynamically. So I am enabling the show plan:

SET SHOWPLAN_ALL ON

Now I am executing the stored procedure :

EXECUTE uspGetEmp 1

Consider on the 4^th row of StmtText:

Compute Scalar (DEFINE :( [Expr1003]=

CASE

WHEN [@ntSort_Order]=(1)

THEN CONVERT_IMPLICIT (bigint, [master].[dbo].[tblEmployee].[ntAge],0)

ELSE [master].[dbo].[tblEmployee].[ntEmpID]

END))

I have few problems in this execution plan:

1. We know the value of variable @ntSort_Order is 1 and for this select query its value will never change. Then why sql server is checking the value of variable @ntSort_Order is 1 or not for each row? If table has 1000k records then it will check 1000k times. Seems, It is unnecessary query cost.

  

2.  For each row sql server is type casting the column ntAge to BIGINT by using operator CONVERT_IMPLICIT since data type of column ntEmpID is BIGINT. But in this case there is not my need to type cast the ntAge to BIGINT.

So I am writing the above stored procedure in some different way:

CREATE PROC uspGetEmp_Second(

     @ntSort_Order AS INTEGER

)

AS

BEGIN

     IF @ntSort_Order = 1 BEGIN

          SELECT

              ntAge, ntEmpID

          FROM tblEmployee

          ORDER BY ntAge

     END ELSE BEGIN

          SELECT

              ntAge, ntEmpID

          FROM tblEmployee

          ORDER BY ntEmpID

     END

END

Now I am comparing the execution cost of above two stored procedures by using the actual execution plan:

EXECUTE uspGetEmp 1

EXECUTE uspGetEmp_Second 1

Actual execution plan:

Here execution cost of first procedure is 97% compare to the second procedure. It is due to second procedure has not any sorting cost since it is using the sorting of non clustered index NCI_Age.

Let's check it for different parameter:

EXECUTE uspGetEmp 2

EXECUTE uspGetEmp_Second 2

Actual execution plan:

Here execution cost of the first procedure is 85% compare to second procedure which is only 15%. It is due to second procedure has not any sorting cost since it is using the sorting of clustered index.

So we can conclude that first procedure is not able to reuse the sorting of index. 

Note: In sql server indexes are sorted according to sort order of key columns.

Stored procedure parameters in sql server
Sql server temporary stored procedures
Foreign key constraint sql server | Referential constraint
IF statement or condition in WHERE clause of SELECT statement in sql server
Sql server create unique constraint which allow multiple NULL values
Dynamic ORDER BY clause in sql server