Great article. One key advantage of CTEs not mentioned is that you can effectively debug/ unit test them by just running select * from CTE immediately after the CTE and check the results rather than having to comment out the outer queries.
The first example could also have been achieved using SUM() OVER () which I think is a cleaner syntax but as this is an article about CTEs we can let it go.
Instead of writing two CTEs, one for each semester, you could have had a single CTE with the semester in the dataset and then joined to it twice using the semester column as a filter in the join clause.
One other trick with CTEs us that normally you can't use windowed functions like RANK() in the WHERE clause, but if you wrap the initial query as a CTE and give the RANK() result a column name you can use it in the main query WHERE clause.
All fields returned by a CTE have to have a unique column name.
CTEs are not always the most performant solution, at least on MSSQL, in my experience very large CTE results sets seem to put pressure on memory and can also stop the query optimiser from using indexes and table statistics when generating a query plan. As there are no indexes bon CTEs you can often get better performance by persisting the data to temp tables and building indexes on them
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Great article. One key advantage of CTEs not mentioned is that you can effectively debug/ unit test them by just running select * from CTE immediately after the CTE and check the results rather than having to comment out the outer queries.
The first example could also have been achieved using SUM() OVER () which I think is a cleaner syntax but as this is an article about CTEs we can let it go.
Instead of writing two CTEs, one for each semester, you could have had a single CTE with the semester in the dataset and then joined to it twice using the semester column as a filter in the join clause.
One other trick with CTEs us that normally you can't use windowed functions like RANK() in the WHERE clause, but if you wrap the initial query as a CTE and give the RANK() result a column name you can use it in the main query WHERE clause.
All fields returned by a CTE have to have a unique column name.
CTEs are not always the most performant solution, at least on MSSQL, in my experience very large CTE results sets seem to put pressure on memory and can also stop the query optimiser from using indexes and table statistics when generating a query plan. As there are no indexes bon CTEs you can often get better performance by persisting the data to temp tables and building indexes on them