Hi all,

Just stumbled upon this. just an FYI,

http://www.microsoft.com/sql/yukon/productinfo/top30features.asp

 Shridhar

Tue, 10 Feb 2004 17:38:26 +0530
Shridhar Daithankar <shridhar@frodo.hserus.net>
MS SQL features for new version
> Just stumbled upon this. just an FYI,
> 
> http://www.microsoft.com/sql/yukon/productinfo/top30features.asp

Also, kick-arse Oracle analytic features:

http://www.akadia.com/services/ora_analytic_functions.html

Chris


Tue, 10 Feb 2004 22:54:08 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: MS SQL features for new version
Shridhar Daithankar wrote:
> Just stumbled upon this. just an FYI,
> 
> http://www.microsoft.com/sql/yukon/productinfo/top30features.asp
> 
>  Shridhar

 From the page:

 > A new Snapshot Isolation (SI) level will be provided at the
 > database level. With SI, users will be able to access the
 > last committed row using a transitionally consistent view
 > of the database. This capability will provide greater
 > scalability for very large database (VLDB) implementations.

Is Snapshot Isolation == MVCC ?


Tue, 10 Feb 2004 10:23:06 -0500
Rodrigo <rdeleonp@yahoo.com>
Re: MS SQL features for new version
Shridhar Daithankar wrote:

>Hi all,
>
>Just stumbled upon this. just an FYI,
>
>http://www.microsoft.com/sql/yukon/productinfo/top30features.asp
>  
>

Notice the Snapshot Isolation. Sounds like MVCC for MSSQL?

Regards,
Andreas



Tue, 10 Feb 2004 18:39:49 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: MS SQL features for new version
> >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.



Tue, 10 Feb 2004 13:20:43 -0500
Rod Taylor <pg@rbt.ca>
Re: MS SQL features for new version
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;


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


10 Feb 2004 15:37:29 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: MS SQL features for new version
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.


Tue, 10 Feb 2004 15:40:31 -0500
Rod Taylor <pg@rbt.ca>
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


10 Feb 2004 16:52:01 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: MS SQL features for new version
Can't they just let the old index continue to work while generating the new
index and then after the new index is created switch? I know the details are
more complex but what would be the factor(s) preventing this?

Keith


-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Rod Taylor
Sent: Tuesday, February 10, 2004 2:41 PM
To: Robert Treat
Cc: Andreas Pflug; shridhar@frodo.hserus.net; PostgreSQL Development
Subject: Re: [HACKERS] MS SQL features for new version


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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Tue, 10 Feb 2004 16:14:51 -0600
"Keith Bottner" <kbottner@comcast.net>
Re: MS SQL features for new version
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.
>  
>

Just a thought:
Creating an index might be performed as a two step mechanism.
First populate the index, using read-only table data and thus creating a 
preliminary index for a snapshot, maybe performing several rounds.

Next, do the rest, now with locking, to get up-to-date.

This way, the index creation impact on ins/upd/del would be minimized.

Regards,
Andreas



Wed, 11 Feb 2004 14:18:59 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Improved index creation (was:MS SQL features for new version)
On Tue, 10 Feb 2004, Rodrigo wrote:

> Shridhar Daithankar wrote:
> > Just stumbled upon this. just an FYI,
> > 
> > http://www.microsoft.com/sql/yukon/productinfo/top30features.asp
> > 
> >  Shridhar
> 
>  From the page:
> 
>  > A new Snapshot Isolation (SI) level will be provided at the
>  > database level. With SI, users will be able to access the
>  > last committed row using a transitionally consistent view
>  > of the database. This capability will provide greater
>  > scalability for very large database (VLDB) implementations.
> 
> Is Snapshot Isolation == MVCC ?

I think it goes that MVCC is a kind of snap shot, but snap shotting could 
be provided by more ways than just MVCC.

But I'm not 100% certain on that.


Tue, 17 Feb 2004 09:55:31 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: MS SQL features for new version