Common Table Expressions
This feature was introduced on SQL Server 2005. It’s a great way to query another query on the fly. I prefer using these over derived tables (DTs) because it provides more flexibility. Some people report better performance using Common Table Expressions (CTEs). I’ve seen and heard both though (that DTs are faster), but I suppose it depends. Just test it out and see for yourself.
Anywhoot, let’s play with CTEs. First let’s create two tables with dummy data.
CREATE TABLE Records ( RecordID INT IDENTITY(1, 1) PRIMARY KEY, RandomData VARCHAR(100) ) DECLARE @t INT SET @t = 0 WHILE @t < 1000 BEGIN SET @t = @t + 1 INSERT INTO Records VALUES( NEWID() ) END CREATE TABLE Information ( RecordID INT IDENTITY(1, 1) PRIMARY KEY, RandomData VARCHAR(100) ) DECLARE @t INT SET @t = 0 WHILE @t < 1000 BEGIN SET @t = @t + 1 INSERT INTO Information VALUES( NEWID() ) END
Now that we’ve create the dummy tables, here’s a barebones example of a CTE:
WITH Slice1 AS ( -- The results for this query gets put into Slice1 -- It persists for the life of this query. SELECT * FROM Records WHERE RecordID BETWEEN 5 AND 400 ) -- Done creating a virtual table called Slice1, now let's -- query it: SELECT * FROM Slice1 WHERE RecordID > 300
CTE’s real power comes when you create multiple virtual tables then finally query them, joining any virtual table you created:
-- This whole thing is 1 query: WITH Slice1 AS ( SELECT RecordID, RandomData FROM Records ) , -- done creating Table Slice1 Slice2 AS ( SELECT RecordID FROM Records WHERE RecordID BETWEEN 10 AND 20 ) , -- done creating Table Slice2 Info AS ( SELECT RecordID FROM Information WHERE RecordID IN ( 5, 6, 7, 9, 15, 18 ) ) -- done creating Table Info -- Now that we've created all these virtual tables, let's use them together in -- one single query: SELECT RecordID, RandomData FROM Slice1 WHERE Slice1.RecordID IN ( SELECT * FROM Info WHERE RecordID IN ( SELECT RecordID FROM Slice2 ) )
Categories