To improve the table access speed and query performance, we recommend that you create a clustered index for each table. This topic describes how to create, query, and delete clustered indexes in an ApsaraDB RDS for SQL Server instance. This topic also describes the usage notes when you use clustered indexes.
Take note of the following items:
Before you perform high-risk operations, such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an RDS instance.
If you granted permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.
Usage notes
You can create only one clustered index for a table. If a clustered index is created for a table and you want to create another clustered index for the table, an error is returned.
Create a clustered index
For more information, see Create a clustered index.
Query index information
Execute the following SQL statement to query information about all indexes of a table:
USE [$DB_Name];
GO
EXEC sp_helpindex N'[$Table_Name]';[$DB_Name] specifies the name of the database. [$Table_Name] specifies the name of the table whose index information you want to query.
Delete a clustered index
Execute the following SQL statement to delete the [$Index_Name] index from the [$Table_Name] table in the [$DB_Name] database:
DROP INDEX [$Index_Name] ON [$DB_Name].[$Table_Name][$Index_Name] specifies the name of the index that you want to delete.
Update statistical information
Statistical information is used to estimate data distribution and develop efficient query plans for databases. When you create an index in SQL Server, you can use the STATISTICS_NORECOMPUTE option to specify whether to automatically update statistical information.
By default, the STATISTICS_NORECOMPUTE option is set to OFF. The value OFF specifies that statistical information is automatically updated when table data is changed. For more information, see CREATE INDEX (Transact-SQL).
Application scope
ApsaraDB RDS for SQL Server
References
You can use various methods to connect to an RDS instance to execute the SQL statements. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.
For more information about stored procedures that are supported by ApsaraDB RDS for SQL Server, see Stored procedures.