View vs Physical Table
-
I am wanting to build a view that joins Sales tables from 03 to 06. Right now I am limiting the view for just customers who are part of a certain program. Just using 05 data gives me over 1000000 records. Would it be better for performance to build a table with indexes or to stay with the view.
Thanks in advance
-
Not necessarily. Provided your query in the view is using decent indexes and performant, as well as queries using the view also use index on the underlying tables, this would be the better option. Saves space, if correctly written, performs the same as a table. You can also index the view (SQL version restriction as well as other requirements exist though. See BOL for indexed views.
Also, you don't need to limit it in the view to a certain subset as this can be done out the view. This then allows anyquery to use the view.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
an indexed view materialises the data so doesn't save space.
The basic answer is to test and see - I generally don't have much time for views as they will often feature in worst performing sql when I'm tunign applications. They can disguise the potential query from hell beacuse someone does a query or join you never thought about.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Sorry, forgot to mention that indexed view / space thing.
What you say is true as well. At first glance, a query is a simple one. Under the covers, it's a nightmare.
Also, my pet hate is when people do not call a view vw_abc or vwabc. Look it's a table. Wait - it's not.
Personally though, I like them, when done right. Save on rework and in a DW, save on data explosion. To feed a couple cubes from a view is nicer than from a couple similar tables.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I do a fair amount of work with very large tables that have schema bound views. There is a fine line/balance to decide if the view could be used. As Crispin stated, they have their place. One of the interesting things that I've found is often times I have gained query performance against some of my more complex views by forcing the noexpand hint.
As also stated above to do it right take the time to evaluate your design and consider other design options.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
1) If you are using Enterprise Edition, I would go with Partitioned Tables/Indexes, which would provide you an array of benefits.
2) If not, I would used partitioned views. Benefits, but not a good as PTs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply