Community is Great
I have a great respect for so many who contribute to the community, without them I would often struggle in my role for sure.
When "strange events" happen in a busy production environment it can be quite daunting when it seems everyone around is expecting you to have the answer/solution at your finger tips.
I'm indebted to Paul White http://sqlblog.com/blogs/paul_white/default.aspx in confirming I'd found a bug and doing all the hard work including raising a connect item https://connect.microsoft.com/SQLServer/feedback/details/797837/incorrect-results-partitioned-nonclustered-index-with-descending-key
Let me explain: Somewhile back we had an issue with a report which essentially every so often would not return a correct result set. There didn't seem to be a particular pattern and the sql statement involved was very large and complex.
It appeared to be related to a secondary index sort order, however there wasn't time to investigate fully as the business was crying out for a "fix" and the developers provided such by rewriting the queries. On Friday last week the same sort of issue arose again, however this time the stored procedure was small containing only a handful of statements. Essentially the report worked on one day of the week but the following day it did not.
To test this I restored historic backups and showed that although it worked on Thursday it didn't work on Wednesdy, only 2 rows returned instead of 8 - as I said a simple report for once. To cut a long story short the culprit was a single column select from a two table join - one table was partitioned. It was easy to test this query and typically it returned 2 rows when called in the procedure but 6 rows when the isolated statement was called in SSMS.
There were a number of variations that I worked through including forcing joins which changed the results, I was able to test this against SQL 2012 as well as SQL 2008 which is what this production system uses.
I've always been a big fan of Paul's work so I dropped him a mail asking if he could have a look at my problem, I'd figured out that a fix was to change the sort order of an index and was under pressure to apply this. I was concerned I had found a bug and if so what other issues we might have and if changing the index sort order might cause other problems, I'm talking a billion+ GBP billing system here, so only slight pressure < grin >
You can read what Paul found and figured out in his blog and the connect - absolutely awesome and I want to publicly offer sincere thanks for all his hard work.
We will attempt to raise this through our corporate support to add weight to the connect.
As a footnote to this post as essentially it relates to Partitioning I have discovered issues if you make the partitioning index descending, I'll have to properly check this out and blog in detail.