MSDN Tech Talk: T-Sql, CLR and XML
Here are the slides and demo's from Wednesday 21st Sept SQL session I presented at the GPS London office:
PhotoDemo – CLR integration
Mike Taulty also presented on Integration and Reporting Services – I’m sure he will post his slides and demos on his blog soon.
Every time I look at SQL 2005 it continues to impress me with the incredible amount of new stuff in the box. I was presenting about whats new in T-SQL and was playing around with recursive Common Table Expressions (CTE) - they are just so neat! Let me show you just one example from the stuff I showed:
Use this to create a new table and populate with some data for an org chart (I took the original example from a whitepaper):
use TempDB
go
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
Just check the data by running:
select * from employees
Now lets build a simple CTE to return all rows:
WITH Emps
AS
(
SELECT *
FROM Employees
)
select * from Emps
CTE’s are a bit like derived tables and also a bit like views, but they have a couple of advantages: Once a CTE is defined it can be used multiple times in the statement or in subsequent CTE’s – multiple CTE’s can be defined for a statement and are comma separated. CTE’s can refer to themselves or to previous CTE’s – that’s how they become recursive.
Here is a slightly more complex CTE statement that pulls out the employee name and their managers name:
WITH Emps(ID, Name, ManagerID, Salary)
AS
(
select empid,
empname,
mgrid,
salary
from Employees
where empid is not NULL
)
select
E.Name as [Employee Name],
E1.Name as [Manager Name],
E.Salary
from
Emps E join Emps E1 on E.ManagerID = E1.ID
But the real power comes with recursion. To make a CTE recursive requires two parts: an Anchor Member that starts the recursion and a Recursive member that is written in terms of itself. Here is an example that builds the full org chart by walking down from the top manager:
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE mgrid is NULL
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
order by lvl
The challenge with any recursive logic is to ensure it will exit at some point. For recursive CTE’s the exit comes when a recursion returns no data, so in this case when an employee is not a manager of anyone else. SQL 2005 provides a guard against infinite recursion (which is still possible) by providing:
OPTION(MAXRECURSION x)
By default this is set to 100. If the recursive part executes 100 times then the statement will abort and fail. Not what you want, but at least the server doesn’t disappear into a black hole of recursion!
There are many more areas worthy of mention, such as Snapshot Isolation that keeps a dynamic change history in tempdb of changes happening during a transaction, so other read (and write) operations can retrieve consistent data even while the row should be locked (see Kimberly Tripp's white paper for more detail: https://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx ).
Check out the SQL 2005 site for more info: https://msdn.microsoft.com/vstudio/tryit/hosted/sql/default.aspx
Marcus