SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioned Table Mystery


Partitioned Table Mystery

Author
Message
alex_pixley
alex_pixley
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 361
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?
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14421 Visits: 12215
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search