Constraint pk__phieuttncc__74ae54bc primary key clustered là gì năm 2024
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Show Clustered and nonclustered indexes
In this articleApplies to: An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. Note SQL Server documentation uses the term B-tree generally in reference to indexes. In rowstore indexes, SQL Server implements a B+ tree. This does not apply to columnstore indexes or in-memory data stores. For more information, see the SQL Server and Azure SQL index architecture and design guide. A table or view can contain the following types of indexes:
Both clustered and nonclustered indexes can be unique. With a unique index, no two rows can have the same value for the index key. Otherwise, the index isn't unique and multiple rows can share the same key value. For more information, see Create unique indexes. Indexes are automatically maintained for a table or view whenever the table data is modified. See Indexes for more types of special purpose indexes. Indexes and constraintsSQL Server automatically creates indexes when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index. If you configure a PRIMARY KEY, Database Engine automatically creates a clustered index, unless a clustered index already exists. When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a nonclustered index. For more information, see Create primary keys and Create unique constraints. How indexes are used by the query optimizerWell-designed indexes can reduce disk I/O operations and consume fewer system resources. Therefore, these indexes improve query performance. Indexes can be helpful for various queries that contain SELECT, UPDATE, DELETE, or MERGE statements. Consider the query During a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table. When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table. Unlike a table, an index frequently contains very few columns per row and the rows are in sorted order. The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select. SQL Server provides the Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes. |