The T-SQL Language implemented in SQL Server 2005 has been enhanced to provide more power and flexibility. For example you have new key words like PIVOT and UNPIVOT. With these you can now do cross tab queries or ‘Pivot Tables’ right in SQL. One of the things can was difficult todo at the database level was doing pivot tables and users have been doing them in other applications from extracts. Now you don’t need todo that any more.
Here is a sample of using SQL Pivot tables:
First we will use the Adventure works db with the following:
USE AdventureWorks
GO
CREATE TABLE MonthlyPurchaseOrders
(VendorID int NOT NULL,
OrderMonth int NOT NULL,
SubTotal money NOT NULL)
GO
and then insert some data with:
INSERT MonthlyPurchaseOrders
SELECT
VendorID,
DATEPART(m, OrderDate),
SubTotal
From Purchasing.PurchaseOrderHeader
WHERE VendorID IN (3,4,5,6,7,8,9)
GO
Now to actually do something open a new query window and run this code:
SELECT * From MonthlyPurchaseOrders
PIVOT (SUM(SubTotal) FOR OrderMonth In (
[1],[2],[3],[4],[5],[6],
[7],[8],[9],[10],[11],[12])) As X
Its fairly simple SQL but for those family with reporting and data warehouses and the like this kind of functionality allows us to keep data processing as close to the data as possible which as we ALL know is the most effective way to process data.
PIVOT and UNPIVOT allow us to really get more effective information out of our data really enhancing Business Intelligence with two key words.
Ok, so PIVOT and UNPIVOT are cool and IT departments will get excited over that, but lets throw a bone to our developers. For YEARS, developers have asked for one single set of key words in SQL. EVERY OTHER LANGUAGE on the market supports them but until now T-SQL has been um… ‘lacking’.
So with SQL Server 2005 we now have 'TRY... CATCHS...'
You might say, 'What is the big deal?'
Well, the big deal is the reason all the software running on your PC just doesn’t blow up every time you blow on it
AND the same reason a server can be made to be reasonably stable.
The 'TRY.. CATCH...' construct allow a developer to have a bit of code try to execute that they are not sure is going to work
And if it doesn’t work the computer just doesn't freeze and lock up, but is able to recover and do something else. Without 'TRY...CATCH...' one little mishap and the whole thing goes to $#!&*.
Seriously let's try some sample code. In this example the try block attempts to delete a record from the Sales.SalesPerson table in the ‘AdventureWorks’ sample database.
USE AdventureWorks
GO
BEGIN TRY
-- Generate a constraint violation error.
DELETE Sales.SalesPerson WHERE SalesPersonID=275
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrNum,
ERROR_SEVERITY() AS ErrSev,
ERROR_STATE() as ErrSt,
ERROR_MESSAGE() as ErrMsg;
END CATCH
GO
When the code executes SQL Server will find a reference constraint and the delete fails. The catch block will execute and we get a select showing us the error information regarding the error that occurred.
Simple but this really makes Developers jobs easier when writing T-SQL.
Additions like the TRY/CATCH and PIVOT and UNPIVOT provide more flexibility and control to help DBAs and Developers in building better database code.