Create index question

Will creating an index while a database is in use block user access?
Thanks.If you're on SQL Server 2000, or on SQL Server 2005 with any SKU apart from
Enterprise Edition, then creating an index is an offline operation. This
means that creating a clustered index takes an exclusive table lock and
read/write access to the table is blocked. It also means that creating a
non-clustered index takes a shared table lock to block only write access to
the table.
If you're on SQL Server 2005 Enterprise Edition, you can make use of the
various online index operations, which do not hold long-term blocking
lockss. See Books Online for CREATE INDEX for more details.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uwIAu0GzGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Will creating an index while a database is in use block user access?
> Thanks.
>|||It depends on what SQL Server version you are using. SQL Server 2000 and
older will block user access. In SQL Server 2005 it could be an online
operation depending on the kind of index operation you are going to perform.
See Create Index in SQL Server 2005 Books online for more details on what
index operations are online and what are offline.
Bob
"Tim Kelley" wrote:
> Will creating an index while a database is in use block user access?
> Thanks.
>
>|||Hi Tim
Even if you're using SQL 2000, you can always kill off a create index
command without much penalty so why not just try it out & kill the command
if you're blocking users too long? You might be surprised how fast some
indexes on otherwise seemingly large tables can be created..
I often use the script at the URL below with SQL 2000 to monitor blocking
from another session during index creation & just kill off the index
creation if necessary. This often gets the job done without requiring a
system outage.
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2005/10/02/50.aspx
SQL 2005 is a different story of course, due the online indexing
capability..
Regards,
Greg Linwood
SQL Server MVP
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uwIAu0GzGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Will creating an index while a database is in use block user access?
> Thanks.
>