Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Partitioned Table Mystery Expand / Collapse
Posted Thursday, March 27, 2014 1:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 24, 2016 9:06 AM
Points: 228, Visits: 355
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?
Post #1555629
Posted Monday, March 31, 2014 2:39 PM This worked for the OP Answer marked as solution

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:35 AM
Points: 9,829, Visits: 11,899
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.

Post #1556715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse