Clustered indexes physically sort table data by the index key, which speeds up range scans and lookup queries. This topic explains how to create, query, and delete clustered indexes on ApsaraDB RDS for SQL Server, and how SQL Server manages index statistics automatically.
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.
Limitations
Each table supports only one clustered index. Attempting to create a second clustered index on the same table returns an error.
Create a clustered index
For more information, see Create clustered indexes.
Query index information
Run the following statement to list all indexes on a table:
USE [$DB_Name];
GO
EXEC sp_helpindex N'[$Table_Name]';Replace [$DB_Name] with the database name and [$Table_Name] with the table name.
Delete a clustered index
Run the following statement to drop an index:
DROP INDEX [$Index_Name] ON [$DB_Name].[$Table_Name]Replace [$Index_Name] with the index name, [$DB_Name] with the database name, and [$Table_Name] with the table name.
Update statistics
SQL Server uses statistics to estimate data distribution and generate efficient query plans. By default, STATISTICS_NORECOMPUTE is set to OFF, so statistics are updated automatically when table data changes.
To control this behavior when creating an index, set STATISTICS_NORECOMPUTE explicitly in your CREATE INDEX statement. For details, see CREATE INDEX (Transact-SQL).
Application scope
ApsaraDB RDS for SQL Server
What's next
To connect to your RDS instance and run these SQL statements, see Connect to an ApsaraDB RDS for SQL Server instance.
For a list of stored procedures supported by ApsaraDB RDS for SQL Server, see Stored procedures.