Saturday, April 30, 2005

SQL Server 2005 TSQL Enhancements: New APPLY Operator

Suppose you have a query that returns a data set and you want for each row returned you want to invoke a function or query that returns a table (table-valued function or query ), the new APPLY operator allowa you to do this

for example if you have a table Employees(EmpID,Name,ManagerID)
and you have the following function that takes an Employee id as a parameter and returns a table of employees under this employee and their level in the hierarchy


CREATE FUNCTION [dbo].[GetEmps](@EmpID [int])
RETURNS @RES TABLE (
Empid INT ,
EmpName nvarchar(50),
Level INT
)

AS
begin

with Emps_CTE (EmpID,Name,Level)
as
(
select EmpID,Name,0 as Level FROM Employees WHERE ManagerID is null and EmpID=@EmpID
Union All
Select Employees.EmpID,Employees.Name,Level + 1 FROM Employees Join Emps_CTE ON
Emps_CTE.EmpID=Employees.ManagerID

)

INSERT INTO @RES
select * FROM Emps_CTE
return



The following query will return for each employee the employees working under him and their level

SELECT Employees.EmpID,Employees.Name ,FollowsEmps.Empid as FollowEmpID,
FollowsEmps.EmpName as FollowEmpName,FollowsEmps.Level as Level

FROM Employees
cross APPLY dbo.GetEmps(Employees.EmpID) as FollowsEmps
end

Wednesday, April 27, 2005

SQL Server 2005 TSQL Enhancements: Common Table Expressions

A CTE is composed of an expression name, optional column list,
and a defining query.
Once defined, the CTE may be referenced in the executing SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement
for example:

use Northwind;
with Customers_CTE
as
(
select ContactName,CompanyName from Customers
)
select * from Customers_CTE


the beautiful thing about CTEs is that you can use them to create recursive queries
say you have the following table Employees(EmpID,Name,ManagerID) and you want to select
each employee and his level in the employees hierarchy (with 0 as the top level) this can
be accomplished with the following CTE

With Emps_CTE (EmpID,Name,Level)
As
(
SELECT EmpID,Name, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL

Union All

Select Employees.EmpID,Employees.Name,Emps_CTE.Level+1 as Level
FROM Emps_CTE join Employees
on Emps_CTE.EmpID=Employees.ManagerID
)
Select * FROM Emps_CTE


Note that the defining query must contain at least two SELECT statements
combined by a UNION ALL operator

Thursday, April 07, 2005

Channel 9's First Birthday

Channel 9 is celebrating its first birthday

Monday, April 04, 2005

Programmers


"If builders built buildings the way that programmers wrote
programs, the first woodpecker that came along would destroy civilization"
- Weinberg’s Second Law
Dijkstra, A discipline of programming, 1976