Re: MS SQL features for new version

On Tue, 2004-02-10 at 15:40, Rod Taylor wrote:
> On Tue, 2004-02-10 at 15:37, Robert Treat wrote:
> > On Tue, 2004-02-10 at 13:20, Rod Taylor wrote:
> > > > >http://www.microsoft.com/sql/yukon/productinfo/top30features.asp
> > >
> > > > Notice the Snapshot Isolation. Sounds like MVCC for MSSQL?
> > >
> > > Actually, the one I noticed was the ability to add or rebuild indexes on
> > > the fly. That is a pretty slick trick.
> > >
> >
> > I was trying to decide how much better this was than
> >
> > BEGIN;
> > DROP INDEX foo ON bar;
> > CREATE INDEX foo ON bar;
> > COMMIT;
>
> Well.. If thats a big table, you've just blocked selects, updates,
> delete, inserts, etc. against that table for the duration of the index
> recreation.
>
> Their text indicates that all activity on the table will not be blocked
> during the creation of a new index on that table. To me, that makes it a
> slick trick.
>

Sorry, I should have written that the other way around-ish...
assume table foo has an index bat on column bar.

BEGIN;
CREATE INDEX baz on foo (bar);
DROP INDEX bat;
COMMIT;

during index creation other folks can select from the table all they
want without being blocked, and upon commit they will switch to the new
index. I've always thought that was a nifty trick, but it's true that
still blocks updates/inserts/deletes, so the m$ feature does sound kinda
slick. :-)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL