Sql-server – CTE placement of Where clause

ctesql server

Is there a difference performance wise between these 2 examples:

WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1      
      WHERE Col3 = '1'
    )

select * from CTE1


WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1      
    )

select * from CTE1
  WHERE Col3 = '1'

We're using a CTE as a subselect within some dynamic sql and can't apply the where clause until the 'select from CTE' happens. Our actual query is much more complicated than this. But, I'm wondering if it will be more performant to not use a CTE. Will the second example actually pull all of the rows from the table and then apply filtering? Or are these functionally equivalent? We use SQL Server.

Best Answer

Both queries have the same execution plan. You can check that in SQL Server Management Studio by typing:

WITH CTE1 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
WHERE Col3 = '1' )
SELECT * from CTE1

;WITH CTE2 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
)
SELECT * from CTE2 WHERE Col3 = '1'

Regarding the second part of your question: it's quite hard to say without looking into actual code and understanding what you are trying to achieve, however you may find that using temporary table may be way more efficient than CTE.

Related Question