Using the EXCEPT clause wrapped in a CTE - SQL

--
--example EXCEPT clause wrapped in CTE
--
WITH RogueEstab
AS
(
SELECT EstablishmentID FROM Establishment
EXCEPT
SELECT JobLog_EstablishmentID FROM JobLog
)
SELECT RE.EstablishmentID, E.EstablishmentName FROM RogueEstab AS RE
INNER JOIN Establishment AS E
ON RE.EstablishmentID = E.EstablishmentID

Auxiliary CTE of Months - SQL

WITH CTE_Months
AS
(
SELECT Mths ='January ' UNION ALL
SELECT Mths ='February ' UNION ALL
SELECT Mths ='March ' UNION ALL
SELECT Mths ='April ' UNION ALL
SELECT Mths ='May ' UNION ALL
SELECT Mths ='June ' UNION ALL
SELECT Mths ='July ' UNION ALL
SELECT Mths ='August ' UNION ALL
SELECT Mths ='September ' UNION ALL
SELECT Mths ='October ' UNION ALL
SELECT Mths ='November ' UNION ALL
SELECT Mths ='December '
)
SELECT Mths FROM CTE_Months

Auxiliary CTE of numbers - SQL

Following on from the previous auxilary CTE of numbers, http://sqlreportingservicescrystalreports.blogspot.com/2009/12/create-common-table-expression-of.html
I realised after reading T-SQL Querying by Itzik Ben-Gan that he's created a much more efficient way of doing it:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;





taken from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139

Create Common Table Expression of numbers - SQL

To create a Common Table Expression (CTE) of numbers for use in queries:


DECLARE @n AS INT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT * FROM Nums