Do you have a complicated query you’re finding it difficult to get your head around? I have found that using Common Table Expressions (CTE) can take some of the pain away, and maybe even improve performance.
A common table expression performs much like an inline view. It does not have a permanent definition stored in the database schema, as views do. It only exists in the query in which it is defined and called. You may only call the CTE once to do any meaningful work, but you can chain together multiple statements in one CTE, utilizing the results of a previous expression, views, tables or tables or functions.
Here is a simple example. Consider a table of user access times. You wish to get the last record for a user.
CREATE TABLE #UserAccess (
ID INT NOT NULL,
UserName VARCHAR(10) NOT NULL,
AccessDate DATE NOT NULL)
INSERT INTO #UserAccess VALUES
(1, 'Ben', '6/3/2013'),
(2, 'Ben', '6/3/2013'),
(3, 'Ben', '6/3/2013'),
(4, 'Steve', '6/5/2014'),
(6, 'Steve', '6/20/2015'),
(7, 'Ben', '6/20/2015')
It’s easy to get the last date by selecting the max value of Access Date Time.
SELECT MAX(AccessDate)
FROM #UserAccess
WHERE UserName = 'Ben'
The answer will be 6/20/2015
However, when you wish to get the Id field of the last date, that requires two queries.
SELECT Id
FROM #UserAccess
WHERE UserName = 'Ben'
AND AccessDate =
(
SELECT MAX(AccessDate)
FROM #UserAccess
WHERE UserName = 'Ben'
)
Here the answer is be 7.
When you want to execute the same query for a set of users it gets even more complicated. What if you want to get the last Id for all Users? So, here is an example of the same query. Instead of creating an inline view like the previous example, I am using a CTE to get the last date per user.
;WITH lastDatePerUser (UserName, AccessDate, RowID)
AS
(
SELECT UserName
,AccessDate
,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY AccessDate DESC)
FROM #UserAccess
)
SELECT ua.Id
,ua.UserName
,ua.AccessDate
FROM #UserAccess ua
JOIN lastDatePerUser la ON ua.UserName = la.UserName
AND ua.AccessDate = la.AccessDate
AND la.RowID = 1
This query returns
Id UserName AccessDate
6 Steve 6/20/2015
7 Ben /20/2015
While I could have created the ordering query as an inline view, I find it easier to follow what is going on using a CTE. Here is how the query would look using an inline query.
SELECT ua.Id
,ua.UserName
,ua.AccessDate
FROM #UserAccess ua
JOIN
(
SELECT UserName
,AcessDate
,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY AccessDate DESC) AS RowId
FROM #UserAccess
) AS la ON ua.UserName = la.UserName
AND ua.AccessDate = la.AccessDate
AND la.RowID = 1
As you can see the filtering query follows the select query. Then the join criteria of the two follows both. In this situation it is too hard to read. However, as the number of rows in your query multiply, or the number of inline views grow, the intention becomes much more difficult to follow.
Here‘s an example of a CTE with multiple queries.
;WITH lastDatePerUser (UserName, AccessDate, RowID)
AS
(
SELECT UserName
,AccessDate
,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY AccessDate DESC)
FROM #UserAccess
)
, lastRowDetails (Id, UserName, AccessDate)
AS
(
SELECT ua.Id
,ua.UserName
,ua.AccessDate
FROM #UserAccess ua
JOIN lastDatePerUser la ON ua.UserName = la.UserName
AND ua.AccessDate = la.AccessDate
AND la.RowID = 1
)
SELECT MIN(Id)
FROM LastRowDetails
This query returns the value 6, which is the last entry for Steve.
For me, I find using the CTEs to be more readable, and the performance is essentially the same, since neither views, inline views, or CTEs are materialized. they are always calculated at the time they are called. If you haven’t picked it up, this is a performance optimization technique as well, when you have a lot of tables to consider in a join. Sometimes you can outsmart the query optimizer; and CTEs are a good way to do it if you know the distribution of your data.
If you like this little tip, or have other questions about SQL queries drop an email to btaylor@sswug.org.
Cheers,
Ben