is the table offline when we create an index?

Is it true that no modifiations include select statements can go through
when an index to a table is created , be it clustered or non clustered
ThanksHmm... yes, sort of.
It seems that when an index is created, SQL Server issues a row-level
exclusive lock on sysindexes, that blocks any updates or selects from
our table, until the creation of the index is completed. The blocking
probably occurs because the query optimizer needs to know what indexes
exists on our table in order to generate a query plan for the
statement.
Razvan|||Mmm... no, not necessarily.
Upon further testing, I saw that some statements may execute, even if
an index on that table is in the process of being created. It seems
that the lock on sysindexes is issued only at the end of the index
creation process. So, if the CREATE INDEX is part of an explicit
transaction that has not been comitted yet, but the index creation is
complete, then it happens like I wrote in the previous message.
Otherwise, when there is no explicit transaction (usually there isn't)
and when the index is really big, then the selects may execute (because
when the optimizer looks in the sysindexes table, it sees no evidence
of the new index, not even a lock, yet). However, certain statements
that modify the table may be blocked (for example, because they try to
get a table-level intent-exclusive lock, but the CREATE INDEX has a
shared lock on our table).
Anyway, it seems that this question is not easily answerable without
some inside knowledge of how SQL Server internally works, so it would
be better if some from MS would comment on this topic.
Razvan

is the table offline when we create an index?

Is it true that no modifiations include select statements can go through
when an index to a table is created , be it clustered or non clustered
Thanks
Hmm... yes, sort of.
It seems that when an index is created, SQL Server issues a row-level
exclusive lock on sysindexes, that blocks any updates or selects from
our table, until the creation of the index is completed. The blocking
probably occurs because the query optimizer needs to know what indexes
exists on our table in order to generate a query plan for the
statement.
Razvan
|||Mmm... no, not necessarily.
Upon further testing, I saw that some statements may execute, even if
an index on that table is in the process of being created. It seems
that the lock on sysindexes is issued only at the end of the index
creation process. So, if the CREATE INDEX is part of an explicit
transaction that has not been comitted yet, but the index creation is
complete, then it happens like I wrote in the previous message.
Otherwise, when there is no explicit transaction (usually there isn't)
and when the index is really big, then the selects may execute (because
when the optimizer looks in the sysindexes table, it sees no evidence
of the new index, not even a lock, yet). However, certain statements
that modify the table may be blocked (for example, because they try to
get a table-level intent-exclusive lock, but the CREATE INDEX has a
shared lock on our table).
Anyway, it seems that this question is not easily answerable without
some inside knowledge of how SQL Server internally works, so it would
be better if some from MS would comment on this topic.
Razvan
sql

is the table offline when we create an index?

Is it true that no modifiations include select statements can go through
when an index to a table is created , be it clustered or non clustered
ThanksHmm... yes, sort of.
It seems that when an index is created, SQL Server issues a row-level
exclusive lock on sysindexes, that blocks any updates or selects from
our table, until the creation of the index is completed. The blocking
probably occurs because the query optimizer needs to know what indexes
exists on our table in order to generate a query plan for the
statement.
Razvan|||Mmm... no, not necessarily.
Upon further testing, I saw that some statements may execute, even if
an index on that table is in the process of being created. It seems
that the lock on sysindexes is issued only at the end of the index
creation process. So, if the CREATE INDEX is part of an explicit
transaction that has not been comitted yet, but the index creation is
complete, then it happens like I wrote in the previous message.
Otherwise, when there is no explicit transaction (usually there isn't)
and when the index is really big, then the selects may execute (because
when the optimizer looks in the sysindexes table, it sees no evidence
of the new index, not even a lock, yet). However, certain statements
that modify the table may be blocked (for example, because they try to
get a table-level intent-exclusive lock, but the CREATE INDEX has a
shared lock on our table).
Anyway, it seems that this question is not easily answerable without
some inside knowledge of how SQL Server internally works, so it would
be better if some from MS would comment on this topic.
Razvan

Is server avaible during reorganize data/index

we are going to setup maintainance plan to reorganize
data/index, say, twice a month. I read somewhere that if
you rebuild index on a clustered index, the server won't
be availble. But I don't know if it's the case when you
reorganize data/index.
Is there any article on this topic?
Thanks
Frankthe server will be available. The table in question will just have an
exclusive table lock on it.
gaj|||So for that table, no records can be delete/updated but new records can be inserted, is that right?|||no.
The lock is Exclusive for Clustered Indexes. Shared lock for NonClustered
Indexes (Users can read data only)
table is totally locked until clustered index is rebuilt.
You can use dbcc indexdefrag Instead of DBCC dbreindex. This does not hold
locks as long as dbreindex. However, it takes longer to run AND IndexDefrag
does NOT automatically update statistics....
Hope this info helps
gAJ
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:ADB20672-6B47-4C3B-B66D-EA8C62A29085@.microsoft.com...
> So for that table, no records can be delete/updated but new records can be
inserted, is that right?