Delete a SQL Index in a table has become fragmented.

Can Anyone tell me what would happen if I delete an SQL Index in an table.
Will it re-index straight away? If so does the database become useable? will
I still be able to add and retrieve data while it re-index's?I assume you're asking about deleting and recreating an
index:
Technically, you could still work with a table using DBCC
DBREINDEX, although I wouldn't recommend it. That
command can hold locks on the table as it rebuilds the
index that would probably affect usage of the tables in
question. Personally, I schedule all reindexing off
hours and check to make sure that no one is logged in.
DBCC INDEXDEFRAG, on the other hand, is intended to be an
online operation. It will not hold locks for long
periods of time, so database usage will be relatively
unaffected.
DBCC INDEXDEFRAG may not be as effective as DBREINDEX --
the latter fully drops and recreates the index.
Check out BOL for more info on each statement.
Hope that helps.
>--Original Message--
>Can Anyone tell me what would happen if I delete an SQL
Index in an table.
>Will it re-index straight away? If so does the database
become useable? will
>I still be able to add and retrieve data while it re-
index's?
>
>.
>|||If you delete a nonclustered index, then the table and other indexes (if
applicable) are not affected.
If you delete a clustered index, then basically, the table is rebuild,
and all nonclustered indexes are rebuild. During this rebuild the
database is still usuable, but the table will be locked.
Gert-Jan
RockStdy wrote:
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable? will
> I still be able to add and retrieve data while it re-index's?|||If I use the DBReindex to reindex the index and then stop it with the
database table do a rollback? Or will I be able to process the table over a
period of time able to stop and start the DBReindex?
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable?
will
> I still be able to add and retrieve data while it re-index's?
>
>|||DBREINDEX is an all or nothing operation. If you stop it before it is
complete it will rollback to the way it was originally. DBCC INDEXDEFRAG is
what you want to use if you want to stop and restart it or if the operation
needs to be online.
--
Andrew J. Kelly
SQL Server MVP
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt2cv9$8vq$1@.news8.svr.pol.co.uk...
> If I use the DBReindex to reindex the index and then stop it with the
> database table do a rollback? Or will I be able to process the table over
a
> period of time able to stop and start the DBReindex?
>
> "RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
> news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> > Can Anyone tell me what would happen if I delete an SQL Index in an
table.
> >
> > Will it re-index straight away? If so does the database become useable?
> will
> > I still be able to add and retrieve data while it re-index's?
> >
> >
> >
>