how to optimize via sql?

[sandoba]

Hello,

is it possible to optimize a tabel in mssql via sql like with "OPTIMIZE TABLE table_name" in mysql? If not, how can I do that in simply sql (without any additional tools, because I use PHP).

Thank you!

[rmiao]

No, sql server doesn't support this command. I think it's similar as rebuild index in mssql.

[skhanal]

In SQL Server you can do the following.

1. Run update statistics to update all statistics on a table

UPDATE STATISTICS authors

Or Rebuild all indexes, You can use

DBCC DBREINDEX (authors, '')

Or recreate the clustered index using DROP_EXISTING clause.

2. Mark the table for recompile so that all other objects referencing this table get recompiled the next time they are executed

sp_recompile authors


3. You can use Enterprise Manager to create a database maintenance plan with optimize option.

[sandoba]

Ok, so it is better to create a central maintenance plan in enterprise manager instead of using this optimize-statement after an larger operation (like in mysql)?

[rmiao]

Sure, you can put all related statements in a script and schedule to run the script as well.