March 27, 2014 at 1:25 pm
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]
FROM dbo.TransactionDetail
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?
March 31, 2014 at 2:39 pm
There isn't any connection between teh partition functiuon and a particular table - you can partition as many tables as you like with the same partition function, the only thing the partition function is "affiliated to" is the type (not even the name) of the input parameter. So you can call the function anywhere. The old table had a column of teh appropriate type, and your code selected that column because it had the same name in both tables. The $PARTITION keyword merely tells the engine that the named scalar function was defined using partition function definition syntax instead of the usual UDF definition syntax. So the function call works just fine and tells you how many rows would go into each partion if you copied the data from the old table to the new.
Tom
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply