common table expression - Use of With Clause in SQL Server

Question

How does with clause work in SQL Server? Does it really give me some performance boost or does it just help to make more readable scripts?

When it is right to use it? What should you know about with clause before you start to use it?

Here's an example of what I'm talking about:

http://www.dotnetspider.com/resources/33984-Use-With-Clause-Sql-Server.aspx

Answer 1

I'm not entirely sure about performance advantages, but I think it can definitely help in the case where using a subquery results in the subquery being performed multiple times.

Apart from that it can definitely make code more readable, and can also be used in the case where multiple subqueries would be a cut and paste of the same code in different places.

What should you know before you use it? A big downside is that when you have a CTE in a view, you cannot create a clustered index on that view. This can be a big pain because SQL Server does not have materialised views, and has certainly bitten me before.