asked
Need to prepare a updateable view
Hi, I have some table which are populated on monthly basis like table201305, table201306, table201307, etc... When I need to fetch some data from these tables everytime I am joining these table using Union all like : **select * from table201305 Union All select * from table201306 union all select * from table201307..** and selecting the data. But now the space is getting huge since table are from every month. Can somebody help me by some updateable view like if I pass the parameter startdate and enddate like 15-april-2013 to 15-may-2013 only data from these two table is selected in the view i.e, **Select * from table201304 union all Select * from table201305** and i can fetch the result. Kindly help me with some updatebale view
If you have constraints that guarantee each table only has data for its date range, SQL Server should do partition elimination and only access the table for the appropriate month (it shouldn't matter how large the tables are in aggregate, only how large a table for a single month is). Read more on partitioned views on technet - [
http://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx][1] or on Simple Talk (Red Gate) - [
https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/][2] Kimberly Tripp has done a very good partitioning video that covers enterprise edition partitioned tables, but it also covers partitioned views which work in standard edition - [
http://technet.microsoft.com/en-US/sqlserver/gg545008.aspx][3] [1]:
http://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx [2]:
https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/ [3]:
http://technet.microsoft.com/en-US/sqlserver/gg545008.aspx
14 People are following this question.
