I've been listening to the
DotNetRocks interview with
Kim Tripp while working this morning and I must say it had some of the best content regarding SQL Server that I have heard in a long time.
I got really jazzed up about some of the new
SQL 2005 T-SQL Enhancements and thought I would do some more research as to what will be improving/changing.
Some of the enhancements discussed by Tripp during the article include:
Optimizing Stored Procedures -> Option Recompile
Case Expressions -> Pivot and UnPivot
Please be sure to
listen - it's some of PWOP's best work so far.
Of all of the enhancements coming in SQL 2005, to me the most exciting are the enhancements involving T-SQL.
New functionality like
Common Table Expressions and
Recursive Queries will allow me to do some very powerful data calculations right from within T-SQL without having to extract my data to another container to do the calculations, summations, and shaping. I use Parent-Child relations on the same table fairly regularly and am excited that I will now be able to view my data in a single result set without having to massage it by iterating and executing ado.net/
LLBLGen methods to get what I want. Look at some of these examples from MSDN and see if you don't agree:
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL
-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
And this makes it even cooler:
Using this level counter you can limit the number of iterations in the recursion. For example, the following CTE is used to return all employees who are two levels below Janet:
WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2
Some other really powerful enhancements come in the way that we are able rank results.
ROW_NUMBER, RANK, DENSE_RANK and NTILE to be more specific offer new and powerful ways to structure are results before they ever leave the db.
Another feature that I believe will completely change my ability to structure resultsets in a much more usable way is the introduction of
PIVOT and UNPIVOT
Consider the following code:
SELECT
itemid,
MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist],
MAX(CASE WHEN attribute = 'name' THEN value END) AS [name],
MAX(CASE WHEN attribute = 'type' THEN value END) AS [type],
MAX(CASE WHEN attribute = 'height' THEN value END) AS [height],
MAX(CASE WHEN attribute = 'width' THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid
That same code now becomes:
SELECT *
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(5,6)
Wow.
And finally, something that should have happened from the beginning - Exception Handling - more importantly structured exception handling:
Meet T-SQL Try Catch
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)
PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'
/* perform corrective activity */
END CATCH
[UPDATE]
I also forgot to mention perhaps the best (albeit simple) enhancement - fixing the TOP directive.
You can now do this:
WHILE 1=1
BEGIN
DELETE TOP(10000) FROM Sales
WHERE dt < '20000101'
IF @@rowcount <10000 BREAK
END
and you can also do
DECLARE @n AS int
SET @n = 5
SELECT TOP(@n) * FROM Orders ORDER BY OrderID
and better still
CREATE PROCEDURE GiveMeTop(@Top int) AS
SELECT TOP(@Top) * FROM Orders ORDER BY OrderID
I have been waiting for that last one A LONG TIME!!!