July 23, 2013 at 3:40 pm
So you are running MS SQL non enterprise edition, meaning you can not utilize true partition tables. You have a 1 billion record sales fact table that contains 5 years of data called Sales_Table. You decide to break this table into 5 different ones, one for each year :
Sales_Table_Y01
Sales_Table_Y02
Sales_Table_Y03
Sales_Table_Y04
Sales_Table_Y05
Under the assumption that each table is properly indexed, you now need to create a one location for existing code to hit all the data, thus we think of a view. So you make a view called Sales_Table so past code is seemless. The question at hand, is how to construct this view so it runs better or just as good as the 1 billion record table. Keep in mind throwing union statements such as the following will not allow for schemabinding to index the view:
select col1, col2 from Sales_Table_Y01
union all
select col1, col2 from Sales_Table_Y02
union all
select col1, col2 from Sales_Table_Y03
union all
select col1, col2 from Sales_Table_Y04
union all
select col1, col2 from Sales_Table_Y05
Thanks in advance.
July 24, 2013 at 8:29 am
I reckon that you should put contiguous check constraints on the date field in each table, as if you were doing a real partitioned table. Can't do any harm, and may even do some good!
July 24, 2013 at 8:46 am
Can you provide examples? In past my tables are at a week level and there isn't any actual datetime data types, just int. i.e. Year=2014 Week = 5
July 24, 2013 at 9:03 am
slash.young (7/23/2013)
So you are running MS SQL non enterprise edition, meaning you can not utilize true partition tables. You have a 1 billion record sales fact table that contains 5 years of data called Sales_Table. You decide to break this table into 5 different ones, one for each year :Sales_Table_Y01
Sales_Table_Y02
Sales_Table_Y03
Sales_Table_Y04
Sales_Table_Y05
This works, I have used it. I like to call it "poor's man partitioning" 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 24, 2013 at 9:12 am
(I'm talking here about a partitioned view rather than a partition scheme, which came more recently.)
I'm very rusty on this, as it's a number of years since I've used this, but below is my recollection.
The partitioned view is constructed like the UNION ALL query you've mentioned. But the magic only comes when certain other constraints are respected. One of these conditions is the check constraints on each of the underlying tables.
These are placed on the partition key, which is the date in your case, and they guarantee to the optimiser that it doesn't have to run through all the tables in order to execute a query i.e. it can go straight to the correct table(s), when you query using the partitioned key. In fact you shouldn't even see the other tables mentioned in the query plan, if all is well.
Is the above functionality restricted to Enterprise version? I can't recall. I'm pretty sure Partition Schemes is enterprise only. But even if it's not included in Standard edition, you can still build your view in the same way, and the fact of implementing the check constraints is certainly not going to be detrimental.
Look up Partitioned Views in BOL, and see what comes up.....hope this helps.
July 24, 2013 at 9:18 am
July 24, 2013 at 10:06 am
David, thanks for the help, that article is what I am really after. I am wanting a solution that hits on the tables needed all through one view.
Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply