John
What is a Common Table Expression (CTE)?
Funny side story, I would call them a “With Statement“. Jokes aside, a CTE is a temporary dataset that can be used and only lives until the end of an execution. The MSDN definition can be found here.
Why use a CTE?
- It allows you to easily reuse code
- It can join on itself
- It can help organize code
Examples:
Helpful Hints of CTE’s
- Make sure any code prior to the WITH statement ends with a terminator (semicolon)
- The dataset only lives during the duration of the execution
- You can only reference the CTEs in a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement (link). For example, this would be invalid:
Hopefully that helps explain the use of CTEs. Happy coding!
