December 10, 2009 at 7:33 am
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.
December 10, 2009 at 9:00 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy