P74 (8/5/2014)
Hi Lynn, I do use CTEs, but i've never specified the column names in that way... i just normally go "with myCTE as (select col1,col2 from tab1)". is this bad practice?thanks again guys for the tips, i found these two articles very interesting:
http://www.sqlservercentral.com/articles/T-SQL/74118/
http://www.sqlservercentral.com/articles/T-SQL/62867/
cheers
😀
Just to be sure, that first article is about the performance pitfalls of "Recursive CTEs" (rCTE for short). Neither Lynn or I have used rCTEs. Instead, we've used versions of Itzik Ben-Gan's "Cascading CTEs", which are quite fast and resource efficient. In fact, they produce exactly ZERO reads by themselves.
As for your question, your method of writing CTEs is just fine. Lynn and I wanted to create a column name for the constant (1) and for the final ROW_NUMBER in the CTEs. We could have done that in the SELECTs inside the CTE or like we did outside the CTE. In this case, we did it just for easier, more consistent formatting because we only need a single column alias. There is no "best practice" in this particular area. Rather, "It Depends" comes into play for many reasons.
--Jeff Moden
Change is inevitable... Change for the better is not.