Remove a Cursor From a Stored Procedure - Part 2

Why is it so slow?!?!?

In my last post, I used a stored procedure from a database I manage to illustrate a real world example of converting a cursor into set based SQL. In this post I will look into a the mechanics behind the scenes and explain at least one of the reasons why it is generally unwise to use cursors when a set based query will provide the same results.

Remove a Cursor From a Stored Procedure - Part 1

A huge problem DBAs encounter with system performance is the ever-present cursor. While there are some scenarios where cursors are unavoidable (or at least difficult to avoid), many instances are down to the developer being more comfortable writing cursors - or some myths as to why a cursor is better. That said, here is a short post highlighting some cursor pros or (non-cons): When are TSQL Cursors the best or only option?

In my organisation, and no doubt many others, the problem stems from the fact that the developers writing SQL are not SQL developers. Rather they are [insert relevant coding language] developers. And in many programming languages processing is best performed on a row-by-row basis. Not so SQL, especially T-SQL.

Comparing Rows in a Table

As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().

To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.

And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.

I will definitely be employing this

A Quick Cheat to Create a Comma Separated List

The select statement below is an easy way of creating a comma separated list within a larger select query without the need for a cursor, user defined function or even a paramenter. It exploits the 'for xml' statement. By not defining any xml elements the column you select gets concatenated into a single line.

In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.

Calculating British Summer Time (BST)

I ran into a problem where time sensitive data was being sent to Sales staff. Our servers are permanently set to Greenwich Mean Time (GMT) to avoid problems with scheduled tasks on the days when the clocks change. Sales staff, however were continually baffled by the GMT times when we were in BST time. So I wrote a User Defined Scalar Function to return an bit value indicating whether a date falls within BST or not.

The function returns 1 if the date falls within BST
The function returns 0 if the date falls outside BST

BST is the same as European Summer Time. Clocks move forward and hour on the last Sunday of March and move back again on the last Sunday of October.

I've modified this function to calculate Daylight Saving Time in the United States (USA) - See below. Clocks move forward and hour on the second Sunday of March and move back again on the first Sunday of November.

UPDATE: Based on the comment from Howard (see below): Please use this script:
/*****************************
My old and clumsy script:
*****************************/
Britain and Europe:
United States:
Example of usage:

Padding a String With a Zero (0)

I constantly had the problem that I needed to use DATEPART to create filenames and other customized strings from dates. The problem is that DATEPART(mm,GETDATE()) will return the following results:

• 1 for January
• 2 for February
• 3 for March

instead of 01 for January, etc.

So, for the purposes of sorting files by dates it is no good. In order to pad the datepart with a leading zero you need to use the RIGHT command. It's defined in BOL as:
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression
Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. integer_expression can be of type bigint.

An example for my DATEPART predicament is as follows:
It's a good idea to convert the DATEPART from an integer to a string (though the RIGHT command does implicitly convert to a string). Limit it to two characters in length and by specifying a that you use 2 of the expression's characters makes sure that you don't add the leading zero to the months/days above 9. Make sense?

For a more generic example that I found on:
http://classicasp.aspfaq.com/general/how-do-i-pad-digits-with-leading-zeros.html
Obviously, if you need trailing zeroes you can use the LEFT command. It works in the same way.

Function for Determining the First and Last Days of Calendar Months

This Scalar-Valued Function below returns the last day of the month for the date passed into it. For example running:

SELECT [dbo.][fn_GetLastDayOfMonth] ('20090320')

 will return '2009-03-31 00:00:00.000'.

So here's the function:

CREATE FUNCTION [dbo].[fn_GetLastDayOfMonth] (@pInputDate datetime)RETURNS datetime    BEGIN        DECLARE @vOutputDate datetime        SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS decimal(12, 5))) - (DAY(@pInputDate) - 1) AS datetime)        SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))        RETURN @vOutputDate    END

 As a further example, I have a report that needs to be run for last month's numbers.
That is, in May I need to run the report for April. Therefore I create the variables @stardate and @enddate as follows:

DECLARE @startdate datetimeDECLARE @enddate datetimeSET @startdate = (SELECT    DATEADD(dd, 1, [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -2, GETDATE())))                 )SET @enddate = (SELECT  [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -1, GETDATE()))               )

Or for SQL Server 2008+

DECLARE @startdate datetime = (SELECT   DATEADD(dd, 1, [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -2, GETDATE())))                              )DECLARE @enddate datetime = (SELECT [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -1, GETDATE()))

So, if it's May, 2009 the variables returned are: @startdate = '2009-04-01'@enddate = '2009-04-30'

This function was found at www.sql-server-helper.com.