I experienced a situation where a query run directly in SSMS was giving a different result set than the same query run from code on a web server, using the same database.
After spending a lot of time ensuring the queries were identical, I ran
DBCC FREEPROCCACHE on the off chance it might help, and to my surprise, it corrected the issue. My understanding was that running this command should only affect performance, not the data set returned.
Am I missing something here?
By different results, I mean the same number of rows were returned with different numeric values.
The query that was giving incorrect results was using this TVF, which is what generates the data that was incorrect:
alter function [dbo].[tfMAPAE_ARevenue] (@start datetime) returns table as -- M-monthly AP-account portfolio A-actual E-external ARevenue return ( select AccountPortfolioID, M01, M02, M03, M04, M05, M06, M07, M08, M09, M10, M11, M12 from ( select isnull(pr.AccountPortfolioID, - pr.CompanyID) as AccountPortfolioID, ear.Amount, 'M' + right(cast(datediff(mm, sd.Start, ear.YearMonth) + 101 as varchar(3)), 2) as ColName from dwvMPRAEARevenue ear inner join Project pr on (ear.ProjectID = pr.ProjectID) -- force to the 1st day of the month inner join ( select cast(floor(cast(@start as float)) - (day(@start) - 1) as datetime) as Start ) sd on (1 = 1) where sd.Start <= ear.YearMonth and ear.YearMonth < dateadd(yy, 1, sd.Start) ) ear pivot(sum(ear.Amount) for ColName in (M01, M02, M03, M04, M05, M06, M07, M08, M09, M10, M11, M12)) aear )