Introduction to Common Table Expressions
-
I agree on readability, which is very important for maintaining code for developers.
One item I have found is that in certain situations referencing a CTE over and over again some how slows down the query. I have found that the calls to the cteFilteredSearchList are slower then going to the table directly dbo.SearchList or a view. Just what I have found from using CTEs for search results.
Example:
;with cteFilteredSearchList
as
(
Select....from dbo.SearchList where Active='1'
),
cteLookupByX
as
(
select .... from cteFilteredSearchList where ....
)
cteLookupByY
as
(
select .... from cteFilteredSearchList where ....
)
Then union the results and review what came back.
Over 12yrs in IT and 10yrs happily stuck with SQL.
- SQL 2008/R2/2012/2014/2016/2017
- Oracle 8/9/10/11
- MySQL 4/5 and MariaDB -
Thank you. I now understand the basics of CTE's. I've seen them referenced and this article represents my first understanding. I'll reference it as I gain more knowledge.
I can see using a CTE with UNIONS. I frequently write temp-code like this:
Query1
union
Query2
union
Query3
Where all three queries are similar but rather complex, but not so complex that I need a permanent view or even a temp table. With a CTE, I can maintain some readability and not have to worry about dropping the view/temp table afterward.
A fine article. Thanks again.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply