Use of With Clause in Sql Server

Question

Hi friends,

please, help me understand how's that working.

Does it really give me some performance boost or it just helps 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?

Thanks.

UPDATE:

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.

Answer 2

RE: "What should you know about With clause before you start to use it?"

One aspect of it that I found initially surprising is that it is only in scope for the following SQL statement.

Answer 3

Unless you use recursive abilities, a CTE is not better performance-wise than a simple inline view.

It just saves you some typing.

The optimizer is free to decide whether to reevaluate it or not, when it's being reused, and it most cases it decides to reevaluate:

WITH    q (uuid) AS
        (
        SELECT  NEWID()
        )
SELECT  *
FROM    q
UNION ALL
SELECT  *
FROM    q

will return you two different NEWIDs.

Note that other engines may behave differently.

PostgreSQL, unlike SQL Server, materializes the CTEs.

Oracle supports a special hint, /*+ MATERIALIZE */, that tells the optimizer whether it should materialize the CTE or not.