I have partitioned a table into 12 tables, each represent a month. Sales_1 to Sales_12. I joined all of them in a view using union all
The problem is when I put a query asking for a specific date, e.g. 1 nov to 31 dec, the view will read all the data from Sales_1 to Sales_12. I want the view to union only Sales_11 and Sales_12. Or more specific, the SQL Server will only read Sales_11 and Sales_12 table, ignoring the other 10.
Another question is, I create 1 database for each year, and each database consists of 12 months and i want to make a view procedure that can span through these years. i.e. I want the data from 1 Nov 2002 to 5 Jan 2003 then it will union Y2002.dbo.Sales_11 to Y2003.dbo.Sales_12. I want to perform some calculation on these result like group or having. How can I make it?
I've tried those before, and it takes a LOT more of time compared to now it divided into some tables. Transaction reached hundreds of thousand records, and it took a long time to calculate daily sales. NOT to mention when I put the stock flow by joining Sales, Received and Stock Transfer tables.
Views can be specified over multiple databases.
These views can be indexed in MSSQL2K Enterprise/Developer ver.
If you don't have MSSQL2K Enterprise and you need summaries,
why are you splitting tables horizontaly? Vertical split would work better.
I was a little mistaken, indexed view cannot have UNION. See BOL "Creating an Indexed View".
It can be used to bind vertically splitted tables (with join) or for indexing aggregates (group by).
Vertically splitted table (I don't know if this is the right term) are 2 or more tables
with the same primary key (PK,A,B)=(PK,A)+(PK,B). It's something between indexed and nonindexed table
(fast full table scan) and you can also add indexes.
I have found something better, partitioned view( distributed view on checked subrange PK tables ).
See BOL "Creating a Partitioned View".