2

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

hgulyan
  • 8,099
  • 8
  • 50
  • 75

3 Answers3

5

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.

Coxy
  • 8,844
  • 4
  • 39
  • 62
4

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

with is a keyword in SQL which just stores the temporary result in a temporary table. Example:

with a(--here a is the temporary table)
(id)(--id acts as colomn for table a )
 as(select colomn_name from table_name )

select * from a
Nandish
  • 19
  • 2
  • 1
    This is not correct (at the time of writing). CTEs in SQL Server do not materialise the data (things are different in Oracle and PostgreSQL). You can test this for yourself by writing a CTE and the referring to it multiple times later in the query. If you look at the query plan, you'll see that the SQL inside the CTE is executed every time it is referenced, not just once. This can be a hidden performance trap for new players. It *might* be cheaper to materialise the data using a temp table, rather than running the same SELECT multiple times. – DatumPoint Feb 13 '19 at 02:02