TechDays 2008T-SQL Queries: Tips and Techniques Part 1 of 3

I attended in the conference T-SQL: Tips and Techniques by Stephen Forte

It was a very good session, where I learned some tips that I will share with you here.


Common Table Expression

Common Table Expression or CTE for short is a new feature in SQL 2005 that returns a temporary result set. It's like a temporary table that lives in the scope of the statement. This new expression is very useful for recursive and nested queries.

A simple example:

WITH ExampleCTE(ProductName, SellPrice)
AS
(
SELECT Descricao, PVPsIVA * 1.21
FROM Artigo
)
SELECT * FROM ExampleCTE

This returns:

ProductName SellPrice
---------------- ---------------
Product1 242.0000
Product2 411.4000
product3 411.4000

Notice that the names of the columns are the ones that are defined in the CTE. Another way to declare the name of the columns is this:

WITH ExampleCTE
AS
(
SELECT Descricao AS ProductName, PVPsIVA * 1.21 AS SellPrice
FROM Artigo
)
SELECT * FROM ExampleCTE


So... lets see the good stuff, recursion! Lets say... I have a table with the product's family descriptions. And the table is recursive with it self. And another table with products. Something like this:

CREATE TABLEdbo.Products
(
ProductId int,
Product_Name nvarchar(50),
FamilyId int,
)

CREATE TABLE dbo.Family
(
FamilyId int,
FamilyName nvarchar(50),
ParentId int,
)

INSERT INTO Family VALUES (1, 'Clouth', NULL);
INSERT INTO Family VALUES (2, 'Food', NULL);
INSERT INTO Family VALUES (3, 'Drinks', 2);
INSERT INTO Family VALUES (4, 'Meat', 2);
INSERT INTO Family VALUES (5, 'Steak', 4);

INSERT INTO Products VALUES (1, 'Steak XL', 5);
INSERT INTO Products VALUES (2, 'Steak SuperBig', 5);
INSERT INTO Products VALUES (3, 'Coca-Cola', 3);
INSERT INTO Products VALUES (4, 'Jeans XPTO',1);

What I want to know is every product that exists in the family and in the sub-families. The hardest part is knowing the sub-families. So, we do this with the CTE:


WITH FamilyCTE(FamilyName, FamilyId, ParentId)
AS
(
SELECT FamilyName, FamilyId, ParentId
FROM Family
WHERE FamilyId = 4
UNION ALL
SELECT Fam.FamilyName, Fam.FamilyId, Fam.ParentId
FROM Family AS Fam INNER JOIN FamilyCTE ON
FamilyCTE.FamilyId = Fam.ParentId
)

This gives us the the family and all the sub-families recursively of the family Meat. After that, it's just join with the table Products and we have all the products from the family Meat.

SELECT Product_Name, FamilyName
FROM Products INNER JOIN FamilyCTE ON
Products.FamilyId = FamilyCTE.FamilyId

If I want another family, just change the familyId in the CTE.


Another good example is finding the persons in a table that have the same name:

WITH CTEPessoas AS (
SELECT min(pessoaid) AS pessoaid, Nome
FROM Pessoa
GROUP BY Nome
HAVING count(*) > 1)

SELECT *
FROM pessoa join ctepessoas ON pessoa.nome = ctepessoas.nome

This gives us all the persons in the table that have the same name.

You can learn more at MSDN - WITH common_table_expression (Transact-SQL)