Nonclustered Index

Creating a nonclustered index on a large table that
is currently being updated.
Questions:
1. What will happen to the updates?
2. Will SQL Server use the new index after creation
while the system is still running?
Any advice will be greatly appreciated.
Thanks!!!
fragbFrom memory, creating a clustered index requires an exclusive lock on the table. As soon as that is
acquired, the other operations will be blocked during the duration of the index creation. Yes, the
index will be used (when the optimize find it useful) after creation, no re-start is necessary.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"fragb" <fmagbulos@.yahoo.com> wrote in message news:f61601c38304$44ea27d0$a601280a@.phx.gbl...
> Creating a nonclustered index on a large table that
> is currently being updated.
> Questions:
> 1. What will happen to the updates?
> 2. Will SQL Server use the new index after creation
> while the system is still running?
> Any advice will be greatly appreciated.
> Thanks!!!
> fragb|||Hi,
Before the Query Optimizer uses an index, the Query
Optimizer evaluates the index to see if it is selective
enough.
A query is considered highly selective if it returns a
very limited number of rows. A query is considered to have
low selectivity if it returns a high percentage of rows.
Thanx.|||To add to Tibor's response: the same is also true for nonclustered
indexes.
Gert-Jan
fragb wrote:
> Creating a nonclustered index on a large table that
> is currently being updated.
> Questions:
> 1. What will happen to the updates?
> 2. Will SQL Server use the new index after creation
> while the system is still running?
> Any advice will be greatly appreciated.
> Thanks!!!
> fragb