Documentation

Rebuilding Table Indexes

In T-SQL, you reindex all the indexes in the table using the DBCC DBREINDEX statement or ALTER INDEX statement. Currently, Babelfish doesn’t support those statements. Instead, you can run the PostgreSQL REINDEX TABLE statement from a Postgres connection. For information about the PostgreSQL statement, see REINDEX in the PostgreSQL documentation.

If your T-SQL maintenance scripts have a statement like one of the following:

DBCC DBREINDEX ('my_database.dbo.my_table');

or

ALTER INDEX ALL ON my_database.dbo.my_table REBUILD;

then run this equivalent statement, while connected to the PostgreSQL port of your Babelfish-enabled server:

REINDEX TABLE my_database_dbo.my_table; -- For servers using multi-database mode

or

REINDEX TABLE dbo.my_table;             -- For servers using single-database mode

For convenience, you can define a PostgreSQL stored procedure that runs the REINDEX TABLE statement for a specified statement, and call the stored procedure using the T-SQL CALL statement. If you do so, make sure that the permissions to call the procedure are consistent with the permissions to perform DDL operations on the table, and that any dynamically created REINDEX TABLE statements quote all the schema and table names to avoid a malformed statement.

For information about using Babelfish from both the TDS port and the PostgreSQL port, visit the Babelfish website.