This behaviour is by design, as explained in detail on this Connect bug report. The most pertinent Microsoft reply is reproduced below for convenience (and in case the link dies at some point):
Posted by Microsoft on 7/7/2008 at 9:27 AM
Closing the loop . . . I've discussed this question with the Dev team.
And eventually we have decided not to change current behavior, for the
following reasons:
The optimizer does not guarantee timing or number of executions of
scalar functions. This is a long-estabilished tenet. It's the
fundamental 'leeway' tha allows the optimizer enough freedom to gain
significant improvements in query-plan execution.
This "once-per-row behavior" is not a new issue, although it's not
widely discussed. We started to tweak its behavior back in the Yukon
release. But it's quite hard to pin down precisely, in all cases,
exactly what it means! For example, does it a apply to interim rows
calculated 'on the way' to the final result? - in which case it
clearly depends on the plan chosen. Or does it apply only to the rows
that will eventually appear in the completed result? - there's a nasty
recursion going on here, as I'm sure you'll agree!
As I mentioned earlier, we default to "optimize performance" -
which is good for 99% of cases. The 1% of cases where it might change
results are fairly easy to spot - side-effecting 'functions' such as
NEWID - and easy to 'fix' (trading perf, as a consequence). This
default to "optimize performance" again, is long-established, and
accepted. (Yes, it's not the stance chosen by compilers for
conventional programming languages, but so be it).
So, our recommendations are:
- Avoid reliance on non-guaranteed timing and number-of-executions
semantics.
- Avoid using NEWID() deep in table expressions.
- Use
OPTION to force a particular behavior (trading perf)
Hope this explanation helps clarify our reasons for closing this bug
as "won't fix".
The GETDATE and SYSDATETIME functions are indeed non-deterministic, but they are treated as runtime constants for a particular query. Broadly, this means the function's value is cached when query execution starts, and the result re-used for all references within the query.
None of the 'workarounds' in the question are safe; there is no guarantee the behaviour will not change the next time the plan is compiled, when you next apply a service pack or cumulative update...or for other reasons.
The only safe solution is to use a temporary object of some kind - a variable, table, or multi-statement function for example. Using a workaround that appears to work today based on observation is a great way to experience unexpected behaviours in future, typically in the form of a paging alert at 3am on Sunday morning.
This seemed like a relational division problem to me:
DECLARE @results TABLE ( ID INT NOT NULL, PASSED BIT NOT NULL, LEVEL INT NOT NULL, CREATEDATE DATE NOT NULL, PRIMARY KEY ( ID, LEVEL, PASSED, CREATEDATE ) )
DECLARE @levels TABLE ( LEVEL INT PRIMARY KEY )
INSERT INTO @levels
VALUES ( 1 ), ( 2 ) --, ( 3 )
INSERT INTO @results
VALUES
( 100, 1, 2, '1 Oct 2016' ),
( 100, 0, 2, '1 Sep 2016' ),
( 100, 1, 2, '1 Aug 2016' ),
( 100, 1, 1, '1 Jul 2016' ),
( 101, 1, 1, '1 Oct 2016' ),
( 102, 1, 1, '1 Oct 2016' ),
( 102, 1, 1, '1 Sep 2016' ),
( 103, 0, 1, '1 Oct 2016' ),
( 103, 1, 2, '1 Sep 2016' )
,
( 200, 0, 1, '1 Sep 2016' ), -- No pass at all
( 201, 1, 1, '1 Sep 2016' ), -- 1 pass at level 1 only
( 202, 1, 1, '1 Sep 2016' ), -- 1 pass at each level
( 202, 1, 2, '1 Sep 2016' ), -- 1 pass at each level
( 203, 1, 2, '1 Sep 2016' ), -- 1 pass at level 2 only
( 204, 0, 1, '1 Sep 2016' ), -- No pass at either level
( 204, 0, 2, '1 Sep 2016' ) -- No pass at either level
SELECT ID,
CASE
WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
THEN 1
ELSE 0
END overallPass
FROM @results
GROUP BY ID
I have not included the CREATEDATE column in the calculation as it doesn't seem to add a lot. Also, does it really make sense for someone to pass a course at the same level twice (as per ID 100 in your sample data)? Does the order in which they pass the courses really matter? If so, then this could cause a bug in my code where Sum of PASSED >= COUNT ( DISTINCT LEVEL ). Pre-aggregating the data would solve this, eg
SELECT ID,
CASE
WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
THEN 1
ELSE 0
END overallPass
FROM ( SELECT ID, LEVEL, MAX( CAST( PASSED AS INT ) ) PASSED FROM @results GROUP BY ID, LEVEL ) x
GROUP BY ID
If the date really is important, please post back and we'll need a slightly more complicated query, basically the PASSED status associated with the max date per ID.
HTH
Best Answer
sys.master_filesis a DMV for databases, so it is by design that it does not show drives that don't have databases.If you need this functionality, consider writing an xp_cmdshell script inside your job to get this information. You'll need to know Powershell.