Archive

Archive for January, 2013

Another reason to use THROW clause instead of RAISERROR | SQL Server 2012

January 24, 2013 5 comments

In my previous post [link] I talked about the new THROW clause introduced in SQL Server 2012 and how it is different from the existing RAISERROR function.

I just stumbled on one more reason to stop using RAISERROR function and start using the new THROW clause.

There are some SQL statements that throws more than one error message when they go wrong due to some reason.

-> When using RAISERROR function it just returns the last (single) error message and its details, but the previous error message details are not returned by this function.

-> With the new THROW clause you won’t see any issue of omitting the previous errors. And it returns all error details as thrown by the SQL statement itself.

Let’s check this with a small example where we want to take backup of a database, but the folder provided does not exist, and hence it errors out:

Example #1:
On executing the below BACKUP statement we can see we get 2 errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


Example #2:
And when you try to use error-handling by using TRY-CATCH and RAISERROR(), it fetches us only 1 error:

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000)
	SET @msg = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.


Example #3:
But this is not the case with the new THROW clause, as I mentioned previously.
It throws all the errors that are originally thrown by the original SQL statement, like below:

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


Thus, you must consider using THROW clause instead of the RAISERROR function if you are in SQL Server 2012.
But, it still depends upon different conditions and scenarios where you would need either of both the features.

Enhanced OVER() Clause in SQL Server 2012 – Part2

January 2, 2013 8 comments

First of all I would like to wish my readers a very Happy and Prosperous New Year – 2013!!!

This is my 1st post in this new year and I will try to come up with more informative and interesting topics related to SQL Server.

Coming back to the topic, sometime back in Part-1 [link] I discussed about how to use both PARTITION BY & ORDER BY clause within OVER() clause when using it with AGGREGATE functions, like SUM().

Here in this post we will see other features that are added to the OVER() clause.

ROWS/RANGE are new the 2 new clauses that you can use inside the OVER() clause to limit the records within the Partition by specifying start and end within the partition. They use a new set of keywords to define the boundaries within the partition, i.e. PREECEDING, FOLLOWING mixed with UNBOUNDED or CURRENT, or number of rows. The PRECEDING and FOLLOWING rows are defined based on the ordering in the ORDER BY clause of the query.

#1. Using ROWS/RANGE UNBOUNDED PRECEDING with OVER() clause:
Let’s check with 1st example where I want to calculate Cumulative Totals or Running Totals at each row. This total is calculated by the SUM of current and all previous rows.
We will use UNBOUND PRECEDING option here, which means that the window starts from the 1st row till the current row.

USE [AdventureWorks2012]
GO

-- To Calculate Cumulative SUM or Running Totals:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS UNBOUNDED PRECEDING) AS CumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE UNBOUNDED PRECEDING) AS CumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_1

In the Output above you can see the was the SUM is calculated, as we go down all previous and current rows are getting summed at all levels.
Here you can also see a difference between the way ROW & RANGE are calculated, for SalesPersonID=4:
– ROWS shows different and continuous calculation at different levels.
– But RANGE shows same calculation of all 3 records at different levels.


#2. Using ROWS/RANGE CURRENT ROW & UNBOUNDED FOLLOWING with OVER() clause:
Now, let’s say you’ve to do the same calculation, but in reverse order. So, here we will use BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING options to define our Window. This means the window starts from current row and ends at the last row.

-- To Calculate Cumulative SUM or Running Totals, but in REVERSE order:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_2

In the Output above we can see that the Running totals are now in reverse order, compared to the 1st example.
Also, the ROWS & RANGE difference can be seen for the SalesPersonID=4 similar to the 1st example.


#3. Using ROWS/RANGE PRECEDING & FOLLOWING with OVER() clause, without UNBOUNDED:
Now, if you want to calculate the SUM only of those values that are just adjacent to a current row. Here we will use combination of row numbers with CURRENT/PRECEDING & FOLLOWING keywords.
Let’s us check this by following example:

-- To Calculate Moving SUM, by taking 3 moving rows:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSumByRowsPrevCurrentNext
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS MovingSumByRowsCurrentNext2
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_3

In the output above for:
– Column MovingSumByRowsPrevCurrentNext: it calculates the SUM of just one Previous, Current & one Next row.
– Column MovingSumByRowsCurrentNext2: it calculates the SUM of Current and next two rows.


So, by above 3 examples we saw how to use new ROWS & RANGE clauses with a mix of UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, etc. Similarly there could be many different cases and scenarios where we can use there clauses and options.

I will try to post more on these features as I see anything new and exciting around this.

For more info on OVER() Clause check MS BOL: http://msdn.microsoft.com/en-us/library/ms189461.aspx