asked
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?
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
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. [
http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/][1] [1]:
http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/
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.
18 People are following this question.
