On Thu, 2005-12-22 at 09:33, Jim C. Nasby wrote: > On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote: > > Actually, ISTM the trend is going the other way. MySQL has instant > > select count(*), as long as you're only using ISAM. Recent versions of > > No comment. > > > MSSQL use an MVCC type system and it also scans the whole table. Oracle > > is the only one I've found that has any optimisation on this front. > > I think this is more an indication of the power of MVCC over traditional > locking rather than the importance of indexes covering (reading just an > index to satisfy a query). Index covering can be a huge benefit, and I'd > be surprised if MS didn't come out with some way to do it in a future > version. I'm actually a bit surprised they don't do it in SQL2005. I wouldn't mind a "with visibility" switch for indexes that you could throw when creating them for this purpose. But burdening all indexes with this overhead when most wouldn't need it is not, IMHO, a good idea. I seem to remember Tom saying that there was a race condition issue though with updating the table AND the index at the same time, that they could be out of sync for a fraction of a second or something like that. So, if we had this kind of thing, the indexes and / or tables would have to be locked for updates. Again, for a reporting database, no big deal. For a transactional database, very big deal.
