CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Fourat
Fourat

Reputation: 2447

Data changes in a CTE after SELECT query

I have a query like this :

USE [MyDataBase] 
GO
DECLARE @day DATE = '2017-05-18'
DECLARE @camp VARCHAR(500) = '9015';
WITH ODCALLS AS (SELECT * FROM [dbo].[ODCalls_2017_05]
   WHERE CONVERT(DATE, CallLocalTime) = @day AND LastCampaign = @camp AND CallType = 1
  )

SELECT COUNT(*) FROM ODCALLS -- this returns 2998 
SELECT DATEPART(HOUR, CallLocalTime) AS dHOUR, COUNT(*) AS [Calls Received]
  FROM ODCALLS
  GROUP BY DATEPART(HOUR, CallLocalTime)
  ORDER BY dHOUR -- this returns 24 rows as there are 24 hours in a day
SELECT COUNT(*) FROM ODCALLS -- this returns 2907

The instance is SQL SERVER EXPRESS 2014 and the data in the OdCalls_2017_05 table doesn't change, here' a preview of the results : enter image description here

I have no idea why this happens, does any one have any explanation ?

Thanks

Upvotes: 1

Views: 287

Answers (1)

rsjaffe
rsjaffe

Reputation: 5730

I'm surprised that works at all. Common Table Expressions do not act like temporary tables. The query is used as input into the next query (like a View can), and optimization may be performed across both the subsequent query and the CTE, so a result set from the CTE may never exist on its own.

Subsequent calls to the CTE shouldn't work, as that violates the definition of a CTE. See https://blogs.sentryone.com/aaronbertrand/backtobasics-ctes/ for more.

Upvotes: 2

Related Questions