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.