Is it possible to seperate or create new views based on periods of time?
I have a view that returns about 240 million rows and I'm trying to cut down the amount of line items returned. I thought about creating multiple views, but they would need to contain different time periods. For example VIEW2013: Jan 1, 2013 - Dec 31, 2013, VIEW2014:Jan 1 2014- Dec 31 2014...etc. Is there a way to do this with views or is this process done another way, and if so what way?
I can think of a couple of options that might help you. 1. Just call the 1 view with different dates in a WHERE clause. 2. Convert the view to a table-valued function with the year as a parameter (or 2 dates as parameters). 3. Convert the view to a stored procedure if you need more complex steps that can't be done in a view or function (like updating a table). If you don't already have an index on the date column, I suggest you add one. Quick performance note: Avoid using scalar functions (built-in or otherwise) in any WHERE clause. This is particularly true if the column(s) referenced by the function is/are part of an index key, but it's a good practice regardless. This is because the optimizer cannot seek to a specific value in an index to improve performance if the column is accessed via a function rather than directly. There are various articles on the internet that explain this well, but I'll link just one here. [
I agree with Everything Tom Staab said. If your database is on an instance with Enterprise Edition, you could also consider partitioning the underlying tables based on a date key. If that's a good option or not depends on how the underlying tables are queried. If most queries have the datekey in the WHERE-clause, it's probably a good idea. When a partitioned table is queried with the datekey (or whatever partitioning key you have) in the WHERE-clause, the SQL Server optimizer will know that the result will only exist in certain partitions. That way, only those partitions who can fulfill the WHERE-condition will ever be touched. This concept is called "partition elimination" and there's a lot to read about it. However - if you have lots of queries doing MAX/MIN or TOP calculations you might take a huge performance hit on partitioning a table and you will end up having to rewrite those queries to much more complex ones. But with that said - if you know that the underlying tables are mostly queried with a partitioning candidate key in the WHERE clause, given the data volumes you have, you might benefit from partitioning the underlying tables.