Advantages and disadvantages of indexes

First, why do you want to create an index (advantage)?
This is because creating an index can greatly improve the performance of the system.
First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.
Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.
Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Ii. disadvantages of establishing a directional index (disadvantage)
Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Although this kind of thought has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is very unwise to add indexes to each column in a table. This is because there is a lot of downside to increasing the index.

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.
Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.
Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

III. guidelines for creating directional indexes
Indexes are built on top of some columns in a database table. Therefore, when you create an index, you should carefully consider which columns you can create an index on, and on which columns you cannot create an index.
In general, you should create indexes on these columns.
First, on the columns that often need to search, you can speed up the search;
Second, on the column that is the primary key, the uniqueness of the column is enforced and the structure of the data in the organization table is arranged;
Thirdly, these columns are mainly foreign keys, which can speed up the connection in the connection columns.
The index is created on columns that often need to be searched by scope, because the index is sorted and its specified range is continuous;
Create an index on a column that is often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time;
The index is often used above the column in the WHERE clause to speed up the judgment of the condition.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics:
First, the index should not be created for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.
Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.
Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has very little value.
The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.
Iv. ways to create an index
There are several ways to create indexes, including methods for creating indexes directly and indirectly creating indexes.
First, create an index directly, such as using the CREATE INDEX statement or using the Make Indexing Wizard.
Second, indexes are created indirectly, such as when a primary key constraint or a Uniqueness key constraint is defined in a table, and an index is also created.
Although both of these methods can create indexes, there are differences in the specifics of how they create indexes.
Using the CreateIndex statement or using the Create Index wizard to create an index is the most basic way to create an index, and this method is most flexible and can be customized to create an index that fits your needs. When you create an index this way, you can use a number of options, such as specifying the fill level of the data page, sorting, collating statistics, and so on, which optimizes the index. Using this approach, you can specify the type, uniqueness, and composition of the index, that is, you can create either a clustered index or a nonclustered index, either by creating an index on one column or by creating an index on two or more than two columns.
You can also create an index indirectly by defining a primary KEY constraint or a uniqueness key constraint. A PRIMARY KEY constraint is a logic that preserves data integrity, which restricts records in the table to have the same primary key record. When you create a PRIMARY key constraint, the system automatically creates a unique clustered index. Although, logically, the primary KEY constraint is an important structure, on the physical structure, the structure corresponding to the primary KEY constraint is a unique clustered index. In other words, on a physical implementation, there is no primary KEY constraint, and only a unique clustered index exists. Similarly, when creating a Uniqueness key constraint, an index is created at the same time, and the index is a unique, non-clustered index. As a result, when creating an index with constraints, the type and characteristics of the index are basically determined, and the user-defined scope is relatively small.
When a primary key or uniqueness key constraint is defined on a table, if a standard index created using the CreateIndex statement is already in the table, the index created by the PRIMARY KEY constraint or uniqueness key constraint overrides the previously created standard index. That is, the index created by the PRIMARY KEY constraint or uniqueness key constraint is higher than the index created with the CREATE INDEX statement.
V. Characteristics of the Index
The index has two characteristics, that is, the uniqueness index and the composite index.
A uniqueness index guarantees that all data in the indexed column is unique and does not contain redundant data. If there is already a primary KEY constraint or uniqueness key constraint in the table, SQL Server automatically creates a unique index when the table is created or when the table is modified. However, if uniqueness must be guaranteed, you should create a PRIMARY key constraint or a uniqueness key constraint instead of creating a unique index. When creating a uniqueness index, you should carefully consider these rules: when you create a PRIMARY KEY constraint or a uniqueness key constraint in a table, SQL Server automatically creates a unique index, and if the table already contains data, then when you create the index, SQL Server checks the table for redundancy of the existing data Whenever you insert data using an INSERT statement or modify data using a modification statement, SQL Server checks the redundancy of the data: if there is a redundant value, SQL Server cancels the execution of the statement and returns an error message, ensuring that each row of data in the table has a unique value. This ensures that each entity can be uniquely acknowledged, and only a unique index can be created on columns that guarantee entity integrity, for example, you cannot create a unique index on a name column in a personnel table because people can have the same name.
A composite index is an index that is created on two or more columns. When searching, when two or more columns are a key value, it is best to create composite indexes on those columns. When you create a composite index, you should consider these rules: You can combine up to 16 columns into a single composite index, the total length of the columns that make up the composite index cannot exceed 900 bytes, which means that the composite column length cannot be too long; In a composite index, all columns must be from the same table, and composite columns cannot be created across tables In a composite index, the order of the columns is very important, so the order of the columns is carefully arranged, in principle, the most unique column should be defined first, for example, the index on (col1,col2) is not the same as the index on (col2,col1), because the order of the two-indexed columns is different ; for the query optimizer to use a composite index, the WHERE clause in the query statement must refer to the first column in the composite index, which is useful when there are multiple key columns in the table; Using composite indexes can improve query performance and reduce the number of indexes created in a table.
Vi. Types of indexes
The index can be divided into two types, depending on whether the order of the indexes is the same as the physical order of the data table. One is a clustered index in which the physical order of the data table is the same as the index order, and the other is a non-clustered index with a different physical order of the data table than the index order.
VII. Architecture of Clustered indexes
The structure of the index is similar to the tree structure, the top of the tree is called the leaf level, the other parts of the tree are called non-leaf levels, and the roots of the tree are in the non-leaf level. Similarly, in a clustered index, the leaf and non-leaf levels of the clustered index constitute a tree structure with the lowest level of the index being the leaf level. In a clustered index, the data page in the table is the leaf level, and the index page above the leaf level is non-leaf, and the index page where the index data resides is non-leaf. In a clustered index, the order of the data values is always sorted in ascending sequence.
Clustered indexes should be created on columns that are frequently searched in the table or on columns that are accessed sequentially. These factors should be considered when creating a clustered index: Each table can have only one clustered index, because the physical order of the data in the table can only be one; the physical order of the rows in the table is the same as the physical order of the rows in the index, and the clustered index is created before any nonclustered indexes are created because the clustered index alters the physical order , the data rows are arranged in a certain order, and the order is automatically maintained; The uniqueness of the key values is either explicitly maintained with the unique keyword or explicitly maintained by an internal unique identifier, which is used by the system itself and cannot be accessed by the user The average size of the clustered index is approximately 5% of the data table, but the actual size of the clustered index varies depending on the size of the indexed column; During the creation of the index, SQL Server temporarily uses the disk space of the current database, and when the clustered index is created, It takes 1.2 times times the size of the table space, so be sure to have enough space to create the clustered index.
When the system accesses data in a table, it first determines whether there is an index on the corresponding column and whether the index is meaningful for the data being retrieved. If the index exists and the index is very meaningful, the system uses that index to access the records in the table. The system browses to the data from the index, and the index browse starts at the root of the tree index. Starting at the root, the search value is compared to each key value to determine whether the search value is greater than or equal to the key value. This step is repeated until a key value larger than the search value is encountered, or the search value is greater than or equal to all the key values on the index page.
VIII. architecture of non-clustered indexes
The structure of non-clustered indexes is also a tree-like structure, which is very similar to the structure of clustered indexes, but it also has a distinct difference.
In a nonclustered index, the leaf level contains only the key values, not the data rows.    Non-clustered indexes represent the logical order of rows. Nonclustered indexes have two architectures: one architecture is to create a nonclustered index on a table that does not have a clustered index, and the other is to create a nonclustered index on a clustered index table.
If a data table does not have a clustered index, the data table is also called the data heap. When a nonclustered index is created at the top of the data heap, the system uses the row identifier in the index page to point to the record in the data page. The row identifier stores information about where the data resides. The data heap is maintained by using an index allocation diagram (IAM) page. The IAM page contains the stored information for the cluster where the data heap resides. In system table sysindexes, there is a pointer to the first IAM page associated with the data heap. The system uses IAM pages to browse and find space in the data heap where new record rows can be inserted. These data pages and the records in these data pages are not in any order and are not linked together. The only connection between these data pages is the order in which they are recorded in IAM. When a nonclustered index is created on the data heap, the leaf level contains the row identifier that points to the data page. The row identifier specifies the logical order of the record rows, consisting of the file ID, page number, and row ID. The identifiers of these rows remain unique. The order of the leaf pages of a nonclustered index differs from the physical order of the data in the table. These key values are maintained in ascending order at the leaf level.
When a nonclustered index is created on a clustered index table, the system uses the clustered key that points to the clustered index in the index page. The cluster key stores the location information of the data. If a table has a clustered index, the leaf level of the nonclustered index contains the clustered key value mapped to the clustered key, rather than the row identifier mapped to the physical. When the system accesses data in a table with non-clustered indexes, and the nonclustered index is created on the clustered index, it first finds a pointer to the clustered index from the nonclustered index, and then finds the data by using the clustered index.
Nonclustered indexes are useful when you need to retrieve data in a variety of ways. When you create a nonclustered index, consider these cases: By default, the indexes you create are nonclustered indexes, and on top of each table, you can create no more than 249 nonclustered indexes, and a clustered index may have at most one.
How the system accesses data in a table
In general, the system accesses the data in the database using two methods: Table scan and Index lookup. The first method is a table scan, which means that the system places the pointer on the data page where the table header data resides, and then scans all the data pages that the table data occupies, one page at a time, in the order in which the data pages are arranged, until all the records in the table are scanned. When scanning, if you find a record that matches your query criteria, select the record. Finally, the records that are all selected to match the conditions of the query statement are displayed. The second method is to use an index lookup. An index is a tree structure that stores a keyword and a pointer to a data page that contains the record where the keyword is located. When using index lookups, the system follows the index's tree structure and finds records that match the query criteria based on the keywords and pointers in the index. Finally, the records that are all found to match the conditions of the query statement are displayed.
In SQL Server, when you access data in a database, SQL Server determines whether an index exists in the table. If there is no index, SQL Server accesses the data in the database using the method of table scan. The query processor generates an optimized execution plan for the query statement based on the statistics of the distribution, aiming to improve the efficiency of accessing the data and determining whether to use table scans or indexes.
Ix. Options for indexing
When you create an index, you can specify options to optimize the performance of the index by using these options. These options include the FILLFACTOR option, the PAD_INDEX option, and the sorted_data_reorg option.
Using the FILLFACTOR option, you can optimize the performance of INSERT statements and modify statements. When an index page becomes full, SQL Server must spend time decomposing the page to make room for the new row of records. Using the FILLFACTOR option, a percentage of free space is allocated on the Leaf index page to reduce the decomposition time of the page. When you create an index in a table that has data, you can use the FILLFACTOR option to specify the percentage of padding for each leaf-level index node. The default value is 0 and the value is equivalent to 100. When an index is created, the internal index node always leaves a certain amount of space, which is sufficient to accommodate records in one or two tables. In tables without data, do not use this option when creating an index, because this option is not meaningful at this time. In addition, the value of this option cannot be maintained dynamically after it is created, so it should only be used when creating an index in a table with data.
The PAD_INDEX option will also use the value of the FILLFACTOR option for the internal index node, so that the internal index node has the same fill level as the leaf index node. If you do not specify the FILLFACTOR option, it is meaningless to specify the PAD_INDEX option separately, because the value of the PAD_INDEX option is determined by the value of the FILLFACTOR option.
When you create a clustered index, the sorted_data_reorg option clears the sort, so you can reduce the time it takes to establish a clustered index. When creating or rebuilding a clustered index on a table that has become a fragment, use the sorted_data_reorg option to compress the data page. This option is also used when you re-need to apply the fill level on the index. These factors should be considered when using the sorted_data_reorg option: SQL Server confirms that each key value is higher than the previous key value, and if it is not high, the index cannot be created SQL Server requires a tablespace of 1.2 times times to physically reorganize the data, use the sorted_data_reorg option to speed up the index creation process by clearing the sorting process, physically copy the data from the table, and when a row is deleted, the space it occupies can be reused ; Create all nonclustered indexes; If you want to populate a leaf page to a certain percentage, you can use both the FILLFACTOR option and the sorted_data_reorg option.
X. Maintenance of indexes
In order to maintain system performance, indexes must be maintained after they have been created, so that the index pages are broken because of frequent operations such as adding, deleting, modifying, and so on.
Using the DBCC SHOWCONTIG statement, you can display the table's data and the fragment information for the index. When you execute the DBCC SHOWCONTIG statement, SQL Server browses the entire index page on the leaf level to determine whether the table or specified index is a serious fragment. The Dbccshowcontig statement also determines whether the data page and index page are full. DBCC SHOWCONTIG statements should be executed on these tables when a large number of changes are made to the table or when a large amount of data is added, or if the table queries are very slow. These factors should be taken into account when executing the DBCC SHOWCONTIG statement: When executing the DBCCSHOWCONTIG statement, SQL Server requires the ID number of the specified table or the ID number of the index, the ID number of the table, or the ID number of the index to be obtained from the system table sysindexes It should be determined how often a DBCC SHOWCONTIG statement will be used, depending on the activity of the table, daily, weekly, or monthly.
Use the Dbccdbreindex statement to reconstruct one or more indexes of the table. Executes the Dbccdbreindex statement when you want to rebuild the index and when there is a primary key constraint or a Uniqueness key constraint on the table. In addition, the Execute DBCCDBREINDEX statement can reorganize the storage space of leaf-level index pages, delete fragments, and recalculate index statistics. These factors should be taken into account when using the Execute DBCCDBREINDEX statement: The system re-populates each leaf-level page based on the specified fill level, or the index of the primary KEY constraint or uniqueness key constraint using the DBCCDBREINDEX statement; using the Sorted_data_ The reorg option allows you to create clustered indexes more quickly, and you cannot use DBCC DBREINDEX statements if you do not have key values aligned; The DBCCDBREINDEX statement does not support system tables. In addition, you can use the Database Maintenance Planning Wizard to automate the process of rebuilding the index.
Statistics are a sample of the column data stored in SQL Server. This data is typically used for indexed columns, but you can also create statistics for non-indexed columns. SQL Server maintains distribution statistics for an index key value and uses these statistics to determine which index in the query process is useful. The optimization of the query relies on the distribution accuracy of these statistics. The query optimizer uses these data samples to determine whether to use a table scan or an index. SQL Server periodically modifies statistics automatically when the data in the table changes. Index statistics are automatically modified, and key values in the index change significantly. The frequency of statistical information modification is determined by the amount of data in the index and the amount of data change. For example, if the table has 10000 rows of data and 1000 rows of data have been modified, the statistics may need to be modified. However, if only 50 rows of records have been modified, the current statistics are still maintained. In addition to automatic system modifications, users can manually modify statistics by executing updatestatistics statements or by sp_updatestats system stored procedures. Use the UpdateStatistics statement to modify all the indexes in a table or to modify a specified index.
Use the SHOWPLAN and Statisticsio statements to analyze index and query performance. You can use these statements to better tune queries and indexes. The SHOWPLAN statement displays each step of the query optimizer used in the Join table and indicates which index is used to access the data. Use the SHOWPLAN statement to view query planning for a specified query. These factors should be taken into account when using the SHOWPLAN statement. The output result returned by the Setshowplan_all statement is more verbose than the output returned by the SET SHOWPLAN_TEXT statement. However, the application must be able to handle the output returned by the Setshowplan_all statement. The SHOWPLAN statement generates information only for one session. If you reconnect SQL Server, you must re-execute the SHOWPLAN statement. The Statisticsio statement indicates the number of inputs and outputs that are used to return the result set and display the logical and physical I/O information for the specified query. You can use this information to determine whether you should rewrite the query statement or redesign the index. Use the Statistics IO statement to view I/O information that is used to process a specified query.
Like SHOWPLAN statements, optimizer shadowing is also used to tune query performance. Optimizer shadowing can provide minor improvements to query performance, and if the indexing strategy changes, the optimizer is useless for hiding. Therefore, limit the use of the optimizer to hide, because the optimizer hides more efficiently and more flexibly. When using the optimizer to hide, consider these rules: Specify the index name, use a table scan when index_id is 0, use a clustered index when index_id is 1, the optimizer hides the query optimizer, and if the data or environment changes, you must modify the optimizer hide.
Xi. Index Tuning Wizard
The Index Tuning Wizard is a tool that analyzes query statements for a series of databases and provides recommendations for using a series of database indexes to optimize the performance of the entire query statement. For query statements, you need to specify the following:
Query statement, which is the amount of work that will be optimized
The databases that contain these tables, in which you can create indexes to improve query performance.
Tables used in the analysis
Constraints that are considered in the analysis, such as the maximum disk space that an index can use
The amount of work that is referred to here can come from two aspects: tracks captured using SQL Server and files containing SQL statements. The Index Tuning Wizard is always based on an already defined workload. If a workload does not reflect the normal operation, then it is recommended that the index used is not the best performing index on the actual workload. The Index Tuning Wizard invokes Query Analyzer and uses all possible combinations to measure the performance of each query statement in this workload. Then, it is recommended to index the performance of the entire query statement throughout the workload. If there is no amount of work to be analyzed by the Index Tuning Wizard, you can use the diagram to create it immediately. Once you decide to track a descriptive sample of a normal database activity, the wizard can analyze this workload and recommend an index configuration that improves the performance of your database.
After the Index Adjustment wizard analyzes the workload, you can view a series of reports, and either enable the wizard to immediately create the best recommended index, or make it a job that can be scheduled, or generate a file that contains the SQL statements that created the indexes.
The Index Tuning Wizard allows you to select and create an ideal combination of indexes and statistics for your SQL Server database without requiring a database structure, workload, or SQL Server

Clustered index non-clustered index

Primary KEY =unique constraint +not null constraint

Unique constraint =unique index so the primary key is indexed

Foreign key to reference the primary KEY or a unique constraint column, so foreign key is also indexed on the referenced column
Clustered index: The logical order of the key values in the index determines the physical order of the corresponding rows in the table. CREATE CLUSTERED INDEX index_id on table name (study number field name)
The clustered index determines the physical order of the data in the table. A clustered index is similar to a phone book, which arranges data by last name. Because a clustered index specifies the order in which data is physically stored in a table, a table can contain only one clustered index. However, the index can contain multiple columns (combined indexes), just as the phone book is organized by last name and first name.
Clustered indexes are particularly effective for columns that are frequently searched for range values. When you use a clustered index to find the row that contains the first value, you can ensure that the rows that contain the subsequent index values are physically adjacent. For example, if a query executed by an application frequently retrieves records from a range of dates, using a clustered index can quickly find the row that contains the start date, and then retrieve all adjacent rows in the table until the end date is reached. This helps improve the performance of such queries. Similarly, if a column is often used to sort data retrieved from a table, you can save costs by aggregating (physically sorting) the table on that column, avoiding sorting each time the column is queried.

When the index value is unique, it is also efficient to use a clustered index to find a particular row.

For example, the fastest way to find a specific employee with a unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

< Span style= "FONT-SIZE:14PX;" > Clustered index is a sort of re-organization of actual data on disk to be sorted by the value of one or more columns specified. Because the index page pointer to the clustered index points to the data page, finding data using a clustered index is almost always faster than using a nonclustered index. Only one clustered index can be built per table, and a clustered index requires at least the additional space of the table 120% to hold a copy of the table and an intermediate page of the index. The idea of building a clustered index is:  
1, most tables should have clustered indexes or use partitions to reduce the competition for the end of the table, in a high-transaction environment, the blockade on the last page severely affects the throughput of the system.  
2, under the clustered index, the data is physically sorted on the data page, and duplicate values are also sorted together, so that they contain scope checks (between, <, <=, >, &gt;=) or use group  By or Order by query, once the row with the first key value in the range is found, the rows with subsequent index values are guaranteed to be physically contiguous together without further searching, avoiding a wide range of scans, which can greatly improve query speed.  
3. When a clustered index is established on a table with frequent insert operations, do not build on columns with a monotonically rising value (such as identity), which can often cause a blocking conflict.  
4, do not include frequently modified columns in the clustered index, because the data rows must be moved to a new location after the code value has been modified.   5, select the clustered index should be based on the WHERE clause and the type of the join operation. The candidate column for the    clustered index is:  
1, the primary key column, which is used in the WHERE clause and the insertion is random.  
2, columns accessed by scope, such as pri_order > 100 and pri_order < 200.  

3. Columns used in GROUP by or order by.

4. Columns that are not frequently modified.

5. The columns used in the connection operation.

Second, the use of non-clustered index (nonclustered indexes)

SQL Server creates an index that is not clustered by default, because the nonclustered index does not reorganize the data in the table, but instead stores the indexed column values on each row and points to the page where the data resides. In other words, non-clustered indexes have an extra level between the index structure and the data itself. A table can have 250 nonclustered indexes if there is no clustered index. Each non-clustered index provides a different sort order for accessing data. When building a nonclustered index, weigh the pros and cons of the index's speed to query and reduce the rate of modification. Also, consider these issues:

1. How much space does the index need to use? 2, the appropriate column is stable.

3, the index key is how to choose, the scan effect is better.

4, whether there are many duplicate values.

Non-clustered indexes on tables require more overhead than clustered indexes and no indexes at all for frequently updated tables. For each row that is moved to a new page, the page-level rows for each nonclustered index that points to the data must also be updated, and sometimes the indexing page may need to be split. The process of deleting data from one page also has a similar overhead, and the removal process must also move the data to the top of the page to ensure the continuity of the data. Therefore, it is very prudent to build non-clustered indexes. Non-clustered indexes are often used in the following situations:

1. A column is often used for aggregate functions (such as Sum,....).

2, a column commonly used in Join,order by,group by.

3, the data of the search is not more than 20% of the data in the table.

To determine the validity of an index:
* Check the WHERE and JOIN clauses of the query. Each column included in either clause is an object that the index can select.
* Experiment with the new index to check its impact on running query performance.
* Consider the number of indexes that have been created on the table.   It is best to avoid having many indexes on a single table. * Check the definition of an index that has been created on the table. It is best to avoid overlapping indexes that contain shared columns.
* Check the number of unique data values in a column and compare that number to the number of rows in the table.   The result of the comparison is the selectivity of the column, which helps to determine if the column is suitable for indexing and, if appropriate, the type of the index. Index type
Depending on the capabilities of your database, you can create three indexes in the Database Designer: Unique indexes, primary key indexes, and clustered indexes.   For more information about the index features supported by the database, see the database documentation. Tip Although a unique index helps locate information, for best performance results, it is recommended that you use a primary KEY or a unique constraint instead.   For more information about these constraints, see PRIMARY KEY constraints and unique constraints. Unique index
A unique index is one that does not allow any two rows to have the same index value.
When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table.   For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.   For more information about unique indexes, see Creating a unique index. Primary key Index

Database tables often have one column or column combination whose values uniquely identify each row in the table.   This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to the data.   For more information about primary keys, see Defining a primary key. Clustered index
In a clustered index, the physical order of rows in a table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index.

If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes

5 The essential difference between clustered index and non-clustered index
The leaf node of the clustered index is the data node, and the page nodes that are not clustered index are still indexed, and a link is left to the corresponding data block.
The insertion speed of the clustered index primary key is much slower than the insertion speed of the non-clustered index primary key.
Whether nonclustered indexes or clustered indexes are suitable for sorting, clustered indexes are just a little faster than nonclustered indexes.
When you need to take out a certain range of data, it is better to use a clustered index than a nonclustered index

Advantages and disadvantages of indexes