I inherited a database that houses years of imports for one of our daily feeds. As it grew over time, they created multiple detail tables (TransactionDetail_2010, TransactionDetail_2011, etc.) and a generic view over top of them (named TransactionDetail). We're moving to one large table partitioned across multiple file groups. I built the new table (TransactionDetail_NEW) on the DEV environment, populated it from the old tables which I then dropped, dropped the view and renamed the table to TransactionDetail. Everything looked good, and I checked my work with this query (from Félix Retama's blog).
SELECT $PARTITION.pfTransactionDetailYear(batchDate) AS [Partition Number] ,
MIN([batchDate]) AS [Min] ,
MAX([batchDate]) AS [Max] ,
COUNT(*) AS [Records in Partition]
GROUP BY $PARTITION.pfTransactionDetailYear(batchDate)
ORDER BY [Partition Number]
Things got strange when I moved to the QA environment. I ran the script to create the partition function, the partition schema and the new partitioned table (still called TransactionDetail_NEW). I then ran the script above but forgot to rename the object in the FROM clause. Even though the partition function is in no way affiliated with the TransactionDetail view and I had yet to populate the TransactionDetail_NEW table, the query returned with a count of "Records in Partition". I know the partition is empty because I just created it from scratch. When I change the FROM clause to look at the TransactionDetail_NEW table, no results are returned (which is what I would have expected). So what is happening here? Why do I get results from a view that has nothing to do with the function? How do I create a valid test?