TSQL: Some interesting usages of Coalesce

The coalesce function is a really powerful function that can help us in various scenarios. It is described in this MSDN document so I am not going to discuss its working in this post. Instead, I will only present a few interesting usages.

  • Calculating overrideable attributes
  • Combining results from Full Outer Join
  • String concatenation

Read the rest of this entry »

Advertisements

TSQL: Error handling with Try Catch from a .NET perspective

Starting with SQL 2005, we can handle unexpected situations in our TSQL scripts/procedures/functions in an structured manner using the famous Try/Catch methodology, similar to what we are used to in our object oriented programming languages (C#, Java, etc). This post will describe certain similarities and how to enjoy the features that look absent at the first look. The MSDN document that describes error handling in TSQL in much detail is: Using TRY…CATCH in Transact-SQL
Read the rest of this entry »

T-SQL: Referring computed/calculated columns in the where and group by clause

I previously blogged about how we can write more manageable and readable TSQL queries using CROSS APPLY operator here. This small post is a follow-up and will describe another scenario. Suppose we have a computed column that is a result of some complex calculation and we want to refer it in a where clause (or a group by clause). This is not straight forward since we can’t use the computed column directly in the where clause like this:

select 
  column1
  ,column2 
  ,computedcolumn = 
    Case 
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
from SomeTable
where computedColumn < 100

The reason is that the Where part of the query is evaluated before the Select part. For this to work, we need to duplicate the whole computation in the where clause like this:

Where
  (
    Case 
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
  ) < 100

Luckily, the APPLY operator comes to our rescue again. We just need to move our calculation to a CROSS APPLY section and we can refer to the calculated column by its alias in the where (as well as group by) clause:

select 
  column1
  ,column2, 
  ,computedcolumn
from SomeTable
cross apply
  (
    Select 
      computedColumn = 
        Case 
          when expression1 = true then column1 * ( column4 - column 5 )
          when expression2 = true then column2 * ( column5 - column 6 )
          when expression3 = true then column3 * ( column6 - column 7 )
          else column4 - column 5
        end
  ) ComputatedColumn1Query
where computedColumn < 100

Great! We can also use a CTE or derived table to accomplish the same:

;With TableWithComputedColumnCalculation as
(
  select 
    column1
    ,column2, 
    ,computedcolumn = 
      Case 
        when expression1 = true then column1 * ( column4 - column 5 )
        when expression2 = true then column2 * ( column5 - column 6 )
        when expression3 = true then column3 * ( column6 - column 7 )
        else column4 - column 5
      end
  from SomeTable
)

select * 
from TableWithComputedColumnCalculation
where computedColumn < 100

Notice that all the approaches I mentioned here (CROSS APPLY, CTE, derived table) are going to have the same query plan (and hence performance) and it is just a matter of personal preference which one to use.

T-SQL: Calculating cascading computed columns using CROSS APPLY operator

The powerful APPLY operator in Transact SQL is typically used to invoke a table-valued function for each row. Here’s an MSDN article that describes such usage. But a very interesting usage of the cross apply operator is to produce a much readable code in cascading calculations. I was working on an SSRS report a couple of days back and found it really helpful. Let’s have a look how.

Consider this simple data regarding employees, their regular hours, overtime hours, overtime code and regular rate:

With SampleData (Employee, RegHours, OTHours, OTCode, RegRate) as
(
      Select 'Employee1', 100, 10, 'OT1', 10
      union all
      Select 'Employee2', 100, 10, 'OT2', 10
      union all
      Select 'Employee3', 100, 10, 'OT3', 10
)

Select *
from SampleData

/*
Output:

Employee  RegHours    OTHours     OTCode RegRate
--------- ----------- ----------- ------ -----------
Employee1 100         10          OT1    10
Employee2 100         10          OT2    10
Employee3 100         10          OT3    10
*/

Suppose, we need to come up with the following result:

Employee  RegHours  OTHours  OTCode RegRate OTMultiplier OTRate  OTCost  RegCost TotalHours TotalCost EffectiveRate
--------- --------- -------- ------ ------- ------------ ------- ------- ------- ---------- --------- -------------
Employee1 100       10       OT1    10      1.2          12.0    120.0   1000    110        1120.0    10.18
Employee2 100       10       OT2    10      1.4          14.0    140.0   1000    110        1140.0    10.36
Employee3 100       10       OT3    10      1.5          15.0    150.0   1000    110        1150.0    10.45

Here’s how the above columns need to be calculated:

  1. OT Multiplier: To be calculated from OTType
  2. OT Rate: RegRate * OTMultiplier
  3. OT Cost: OTHours * OTCost
  4. Reg Cost: RegHours * RegCost
  5. Total Hours: RegHours + OTHours
  6. Total Cost: RegCost + OTCost
  7. Effective Rate: TotalCost / TotalHours

In the above calculations, the point to be noted is that almost every next column depends upon the result of a previous one. For example, Effective Rate depends upon Total Cost, that depends upon OT Cost, that depends upon OT Rate, that depends upon OT Multiplier, that depends upon the value in OT Type field.

The most straight forward attempt using a simple TSQL query would look something like:

Select *
  ,OTMultiplier =
    Case
      when OTCode='OT1' then 1.2
      when OTCode='OT2' then 1.4
      when OTCode='OT3' then 1.5
    end
  ,OTRate =
    Case
      when OTCode='OT1' then 1.2 * RegRate
      when OTCode='OT2' then 1.4 * RegRate
      when OTCode='OT3' then 1.5 * RegRate
    end
  , OTCost =
    Case
      when OTCode='OT1' then 1.2 * RegRate * OTHours
      when OTCode='OT2' then 1.4 * RegRate * OTHours
      when OTCode='OT3' then 1.5 * RegRate * OTHours
    end
  , RegCost = RegHours * RegRate
  , TotalHours = RegHours + OTHours
  , TotalCost =
    Case
      when OTCode='OT1' then 1.2 * RegRate * OTHours + RegHours * RegRate
      when OTCode='OT2' then 1.4 * RegRate * OTHours + RegHours * RegRate
      when OTCode='OT3' then 1.5 * RegRate * OTHours + RegHours * RegRate
    End
  , EffectiveRate =
    Case
      when OTCode='OT1' then (1.2 * RegRate * OTHours + RegHours * RegRate) / (RegHours + OTHours)
      when OTCode='OT2' then (1.4 * RegRate * OTHours + RegHours * RegRate) / (RegHours + OTHours)
      when OTCode='OT3' then (1.5 * RegRate * OTHours + RegHours * RegRate) / (RegHours + OTHours)
    End

From SampleData

The above query is fairly unreadable and difficult to maintain. It is because the effect of OT Type field is propagated to all the columns. What if a new OT Type is added, or any OT Multiplier logic is changed? We will then need to modify a lot of places. A much better code can be achieved if we use several CTEs or derived tables, but even more readable and maintainable code can be produced by using the cross apply operator. Here’s an example:

Select *
from SampleData
cross apply
(
  select OTMultiplier =
    Case
      when OTCode='OT1' then 1.2
      when OTCode='OT2' then 1.4
      when OTCode='OT3' then 1.5
    end
) as ca_otmultiplier
cross apply
(
  select OTRate = RegRate * OTMultiplier
) as ca_otrate
cross apply
(
  select OTCost = OTHours * OTRate
    , RegCost = RegHours * RegRate
) as ca_otcost
cross apply
(
  select TotalHours = OTHours + RegHours
    , TotalCost = RegCost + OTCost
) as ca_totals
cross apply
(
  select EffectiveRate = TotalCost / TotalHours
) as ca_effective

The great advantage is that the calculated result of every cross apply operator can be used into the next one that follows it. Another advantage as pointed by Brad is that such readability and maintainability comes without any extra cost. So the cross apply approach is a great choice if we have many calculated columns, each of which depends upon the result of previously calculated ones.

T-SQL: Using cursor with Common Table Expressions

Cursors aren’t a bad choice for certain scenarios. Few days back, I was writing a stored procedure that was to be scheduled as a SQL agent job. I needed to send emails for each row of a result set and hence concluded that a Fast Forward cursor (Read Only, Forward only cursor) would be a nice choice. I created several CTEs (common table expressions) to reach my final result set and declared a cursor for my final select statement like this:


--declare some CTEs
;With CTE1 as
(
   --CTE1 definitiion
)
,CTE2 as
(
   --CTE2 definitiion
)

--declare a cursor for final select statement
Declare myCursor Cursor Fast_Forward For
   --select query
   Select ... From CTE2

But I was greeted with the following error:

Incorrect syntax near the keyword 'Declare'.

Ok, so my expected syntax is incorrect. After a quick google, I found that the declare cursor statement needs to be on the top of CTE declarations. So, here’s the correct syntax to define cursor with CTEs:


--declare a cursor above the CTE definitions
Declare myCursor Cursor Fast_Forward For

--declare CTEs
With CTE1 as
(
   --CTE1 definitiion
)
,CTE2 as
(
   --CTE2 definitiion
)

--select query as normal
Select ... From CTE2

--now open and use the cursor and don't forget to close and deallocate it in the end

Cool!! That was a nice learning.

Posted in T-SQL. Tags: , , . 5 Comments »

T-SQL: Using result of a dynamic SQL query in a variable or table

Although, not a recommended practice, but sometimes we have to write our queries using dynamic SQL. In such situations, it is generally needed to fetch the result (scalar or tabular) of dynamic SQL into the main (non-dynamic) query. This is not straight forward because dynamic SQL runs in its own scope and we cannot access the variables defined in main query. This post presents a few approaches to consume the result of a dynamic SQL query:

sp_ExecuteSql stored procedure

This is the most generic and powerful method of invoking dynamic SQL since it allows us to write a parameterized dynamic query with input/output parameters. Here’s a simple example of using sp_executesql to consume the result of a dynamic SQL query:


declare @today datetime
exec sp_executesql
    N'Select @internalVariable = GetDate()', --dynamic query
    N'@internalVariable DateTime output', --query parameters
    @internalVariable = @today output --parameter mapping
select @today

Table variables and Temporary tables

This method is used when we want to get a tabular result set from our dynamic query. Here’s an example to get the result of a dynamically created SQL query by using table variables:


declare @myTable table
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into @myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from @myTable

Here’s the same example that uses a temporary table to fetch the result set of a dynamic SQL query:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into #myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from #myTable
drop table #myTable

Since temporary tables have physical existence so we can refer to the temporary table inside our dynamic SQL query as well. Here’s an example illustrating this technique:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

--dynamic query
exec sp_executesql
    N'insert into #myTable
        select name, database_id, create_date from sys.databases'

select * from #myTable
drop table #myTable

Temporary tables or Table variables can also be used to fetch the result of a stored procedure. Notice that for saving this result, the columns of table variable/temporary table must match with the result of stored procedure. That is, we need to take “ALL” the columns. Here’s an example that grabs the result set from a stored procedure into a table variable.


declare @myTable table
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into @myTable
    exec sp_helpserver

select * from @myTable

Also, here’s an example to get result of a stored procedure using temporary table:


create table #myTable
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into #myTable
    exec sp_helpserver

select * from #myTable
drop table #myTable

Thats all from me. Let me know if you have any more solutions.

TSQL Challenge 12: Completing sequence by inserting missing rows

TSQL Challenge 12 was a relatively easier one. The participants were given month-wise score values and were asked to complete the sequence by creating entries for missing month.

Here’s the sample input:

YearMonth   Score
----------- -----------
200903      100
200803      95
200802      99
200801      100
200711      100

And here’s the desired output. Notice that the score of last month is replicated in each of the missing rows:

YearMonth   Score
----------- -----------
200908      100
200907      100
200906      100
200905      100
200904      100
200903      100
200902      95
200901      95
200812      95
200811      95
200810      95
200809      95
200808      95
200807      95
200806      95
200805      95
200804      95
200803      95
200802      99
200801      100
200712      100
200711      100

The script to generate sample data is provided below:

DECLARE @Scores TABLE
(
	YearMonth	INT,
	Score		INT
)

INSERT @Scores VALUES(200903, 100)
INSERT @Scores VALUES(200803, 95)
INSERT @Scores VALUES(200802, 99)
INSERT @Scores VALUES(200801 ,100)
INSERT @Scores VALUES(200711, 100)

Solution

I once blogged about creating a sequence of numbers/dates using a recursive CTE in this post. The same technique can be used here. However, since the YearMonth column in the sample data is integer, we have two choices:

  1. Convert it to DateTime and apply T-SQL DateTime functions
  2. Leave it as Integer and apply some intelligent arithmetic

I am providing both the solutions here. Note that the first solution will be slower due to overhead of casting and applying T-SQL scalar functions.

The first solution: Converting to DateTime

;with cte as
(
    select score, Cast(Cast(YearMonth as varchar)+'01' as datetime) as dateVal
        from @scores
    union all
    select score, dateadd(month, 1, dateval)
        from cte
        where not exists
            --the resultant YearMonth value should not lie in the original table
            ( select 1 from @scores s where s.YearMonth = cast( left(convert(varchar, dateadd(month, 1, cte.dateval), 112), 6) as int) )
            --stop at current month
            and dateadd(month, 1, cte.dateval) < getdate()
)

select left(convert(varchar, dateval, 112), 6) as yearmonth, score
from cte
order by dateval desc

Explanation:
Here I am simply converting the integer YearMonth column to a datetime dateval column by appending 01 to the end (so a 200901 becomes 20090101 that can easily be cast to a dateTime) and then finding subsequent dates by adding one month in each CTE iteration.

The second solution: Integer Arithmetic

;with cte as
(
    select YearMonth, Score
		from @Scores
    union all
    select YearMonth + YearMonth % 100 / 12 * 88 + 1 as YearMonth, Score
        from cte
        where not exists
            --the resultant YearMonth value should not lie in the original table
            ( select s.YearMonth from @Scores s where s.YearMonth = (cte.YearMonth + cte.YearMonth % 100 / 12 * 88 + 1) )
            --stop at current month
            and cte.YearMonth < month(getdate()) + year(getdate())*100
)

select *
from cte
order by YearMonth desc

Explanation:
The important point is to increment the value of YearMonth correctly. So 200811 should get incremented to 200812 but 200812 should get incremented to 200901. This isn’t difficult if we introduce a case statement like this:

YearMonth + (Case When YearMonth%100 < 12 Then 1 Else 89 End)

But I wanted to do this purely using arithmetic with no Case statements, so I came up with this formula:

(YearMonth % 100 / 12 * 88) + 1

Note that the factor (YearMonth % 100 / 12 * 88) will reduce to zero for all values from January to November, i.e. from 200801 to 200811.

I hope you enjoyed the solution.

Update:
It was pointed out in one of the comments by Rakesh that the solution could reach the default limit of recursion which is 100. In order to avoid this, we need to add option (maxrecursion 12,000) in the final select statement. Then, we can have 10,000 years missing between two adjacent entries. Thanks, Rakesh.

...
select *
from cte
order by YearMonth desc
option (maxrecursion 12,000)