recreate clustered index
Norris
Deon
Deon.
Norris <jch...@cooper.com.hk> wrote in message
news:7v3m8a$1n7c$2...@adenine.netfront.net...
Dan Guzman
Hope this helps.
Arun Kamat
is used for rebuilding suspect/corrupt indexes or building indexes
after changing sort order of the server. If the index is already in good
shape and you have not changed the sort order as well then dbcc reindex
would not recreate the index. You need to drop and create it.
If you do not want hardcoding while re-creating indexes then use reverse
engineering with sybperl to generate 'drop / create index' statements. You
can get the stored procedures at http://www.tiac.net/users/sqltech/
Arun
Deon wrote:
> check out dbcc dbreindex
>
> Deon.
>
Deon
indicated he was using MS Sql 7. Now, if you peek into BOL you would see
info referring to DBCC DBREINDEX that states : DBCC DBREINDEX rebuilds an
index for a table or all indexes defined for a table.
Deon.
Arun Kamat <ann...@tkg.att.ne.jp> wrote in message
news:38159BA3...@tkg.att.ne.jp...
Norris
Deon
Deon
Norris <jch...@cooper.com.hk> wrote in message
news:7v4g8o$2djh$1...@adenine.netfront.net...
Mariano Corral
have been cross-posted to comp.databases.sybase.
I don't know about MS SQL Server, but Sybase
SQL Server (now Adaptive Server Enterprise, ASE)
only drops and recreates an index when
dbcc reindex discovers some kinds of corruptions.
About the question of how to recreate indices,
I would like to contribute with my opinion.
I think that, although reverse engineering
is a good starting point if the original create
statement is lost, it's better to keep the
create statements stored in text files. They
may include things that reverse engineering
cannot guess, as fillfactor (I'm talking about
Sybase). Another reason is that, if the drop
and create process fails, you may loose both
the index and its definition.
Regards,
Mariano Corral
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!
Norris
Mariano Corral
> Actually, I am using PowerDesigner to store the database schema
> but I find that tool cannot generate correct syntax for create
> index statement with some opotions. Is it real that I cannot
> reverse all information about index from sqlserver?Another issue I
> found is I need to a lot space and time recreate clustered
> indexunless using option of sorted_data_reorg which is however a
> MS transact-sql extension.
I know about Sybase SQL Server 11.0.3.3, but little about
11.5.x and 11.9.2.x. Nothing about MS SQL Server. Based on
this experience:
- The fillfactor clause of the create index statement is not
stored in system tables. Then, it cannot be retrieved by
any reverse engineering tool. What SQL Server does is just
leave that percent full while it builds the index.
- The clause sorted_data exists in Sybase, but it works
differently depending on the version. Always assuming
the index being created is clustered: For 11.0.3.3 and
previous, it does relocate data and applies any fillfactor
given to the create index statement. So, sorted_data is
(was) a good way to reorganize table data while saving
a lot of time. However, it does save little (if any) working
space; it's still required to have free space on the
database for an amount equivalent to 120% the size of
the data part of the table.
- For 11.5.x it does rebuild only the index tree, but
leaves data pages intact. So, it doesn't serve the
purpose of data reorganization. sorted_data must be
omited for this purpose.
- For 11.9.2 there's a new command, but only for DOL
tables: reorg.
Regards,
Mariano Corral
Sent via Deja.com http://www.deja.com/
Before you buy.