Jim Davis wrote: >>-----Original Message----- >>From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] >>Sent: Wednesday, February 05, 2003 10:32 AM >>To: CF-Talk >>Subject: Re: all records deleted from table >> >>http://simon.cs.cornell.edu/home/praveen/papers/partindex.de95.ps.Z >> >> >> >>Typical indexing would have a unique index over the primary key field >>without any predicate, and all other indexes only over the current >>records. In this case that would be: >>CREATE INDEX currentRecords_idx >> ON table (field) WHERE dtDeleted IS NOT NULL; > > I may be reading this wrong, but I think that you'd accomplish nearly > the same thing (albeit in two steps) in SQL Server by indexing a view. > > You'd have to take the view into account on the select however... So > many this doesn't do the same thing at all. But it's one way of getting > close at least.
MS SQL Server indexed views (a.k.a. materialized views a.k.a. materialized query tables) are quite different. They depend on a part of the data being stored in another table and that part being maintained by triggers. If you compare that to the scenario from the paper you will notice some differences. For one type of query they are/can be much faster (depends on the presence of joins/aggregates), but they lack the flexibility of partial indexes. And they come at a hefty price once you need more as 1 indexed view, because not only the system that issues the queries has to keep track of more and more tables which are only usefull for specific queries, but each table has to be stored and eats disk space. For heavily indexed tables it is not uncommon to have the indexes take more space as the data itself, now imagine storing that data X times and the having all those indexes. BTW, materialized views can be implemented in any database that can use triggers. I don't really understand why many database vendors insist on naming them views when they are just extra tables. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
