Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
-- 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
share|improve this question

closed as not a real question by Paul White, RolandoMySQLDBA, Mark Storey-Smith, Marian, dezso Mar 15 '13 at 14:28

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

What is the query? Does it use any non deterministic constructs? e.g. ROW_NUMBER on a non unique ORDER BY? – Martin Smith Mar 8 '13 at 18:19
Can you define "different results"? Different number of rows, different values, or just different order? – Aaron Bertrand Mar 8 '13 at 18:23
@MartinSmith The query does not use any columns that are floats, does not use getdate(), but it does have a table valued function that references a table in another database via a synonym. That may be enough for it to be considered non-deterministic, I am not sure. ROW_NUMBER is not being used, it is a pretty simple aggregate with a PIVOT applied. – RedFilter Mar 8 '13 at 18:25
@AaronBertrand I updated my question, should have been more specific with that. Will paste the query shortly. – RedFilter Mar 8 '13 at 18:29
Are you running the query in SSMS and through the web app using the same login/security context? – Jon Seigel Mar 8 '13 at 18:37