Improved index creation (was: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