sql--Index

An index is a table of the corresponding relationship between the column values established in a certain order in a table or columns in a given sequence and the record rows. Indexing in a database system mainly has the following functions:

L fast access to data; L guarantee the uniqueness of data records; L implement referential integrity between tables; When retrieving data by using the order BY and GROUP BY clauses, the index can reduce the time of sorting and grouping. 1. Clustered index

The clustered index sorts the key values of the data rows within the table and stores the corresponding data records, making the data table physical order consistent with the index order. SQL Server organizes clustered indexes by B-Tree (BTREE), and B-tree is built as a tree with multiple nodes. The top node forms the starting point of the index, called the root. Each node contains several values for the indexed column, and each value in one node points to another node or to a row in the table, and the values in one node must be ordered in order. A node that points to a row is called a leaf page. Leaf pages themselves are interconnected, and a leaf page has a pointer pointing to the next group. In this way, each row in the table will have a corresponding value in the index. You can find the row directly based on the index value when you query.

• The leaf node of the B-tree in the clustered index holds the data page information. The clustered index holds the data at the leaf level of the index. This means that regardless of which (or what) fields in the table are in the clustered index, these fields are saved sequentially in the table. Because of this sort, there is only one clustered index per table.  • Because data records are stored in the order of the clustered index keys, the clustered index is useful for locating records. 2. Nonclustered indexes

Nonclustered indexes are completely independent of the structure of the data rows. SQL Server organizes nonclustered indexes on a B-tree basis, unlike clustered indexes where the leaf nodes of a nonclustered index B-tree do not hold data page information, but instead hold key values for nonclustered indexes, and each key-value entry has a pointer to the data row that contains the key value.

Within a nonclustered index, a pointer to a data row from an index row is called a row locator. The structure of a row locator depends on whether the data page is stored in a heap or aggregate. For a heap, a row locator is a pointer to a row. For a table with a clustered index, the row locator is the clustered index key. Only when a clustered index is created on a table, the rows in the table are stored in a specific order, and the rows are stored in the clustered index key order. If a table has only a nonclustered index, its data rows are stored in an unordered heap.

In the PXSCJ database, the XSB, KCB, CJB Three tables are frequently queried and updated. To improve query and update speed, consider establishing the following indexes on three tables:

(1) For the XSB table, a unique index is established by the number of numbers organized as a clustered index.

(2) For the KCB table, the primary key index is established by the course number and organized as a clustered index.

(3) For KCB tables, unique indexes (unique constraints) are created by course name and organized as nonclustered indexes.

(4) For the CJB table, the unique index is established by the number + course number, and the organization is the clustered index.

In SQL Server Management Studio, you can either create the above index with an interface or use the T-SQL command to index through the query parser.

1. Interface way to create an index

Start SQL Server Management Studio, expand Databases in Object Explorer, and select dbo in table. XSB, right-click the index item and select New Index (N) ... menu item on the shortcut menu that pops up.

At this point, the user can enter the index name in the "New index" window that pops up (the index name must be unique in the table), such as PX_XSB, select the index type as "clustered", tick the "unique" check box, click the "Add" button in the new Index window, and in the "Select Table column to add to index key" window, select the column you want to add, and when you are finished, click the OK button to set the relevant properties for the index key column in the main interface, and click the OK button to complete the creation of the index.

The methods for creating indexes in the Table Designer window are as follows.

1th Step: Right-click the "dbo" in the PXSCJ database. XSB table, select the Design menu item in the popup shortcut menu to open the Table Designer window.

2nd step: In the Table Designer window, select the study Number property column, right-click, and select the Index/Key menu item in the popup shortcut menu. Click the Add button in the Index/Key window that opens, and in the Name field in the Identification property area on the right, determine the name of the new index (with the system default or re-name). You can modify the column to create an index by clicking the button after the Columns column in the general properties area on the right. Setting the "is unique" column to "Yes" indicates that the index is a unique index. In the Create as clustered option under the Table Designer bar, you can set whether to create as a clustered index, because the clustered index already exists in the XSB table, so this option is not modifiable.

3rd Step: Finally close the window, click the "Save" button on the panel, click the "Yes" button in the popup dialog box, the index creation is complete.

Once the index is created, simply return to the main SSMs window and expand "dbo" in Object Explorer. XSB the index item in the table, you can view the established index. Other indexes are created in a similar way.

2. Using SQL commands to build an index

Use the CREATE INDEX statement to create an index on a table.

Syntax format:

CREATE [Unique]/* Specify whether the index is unique */

[CLUSTERED | Nonclustered]/* How the index is organized */

Index index_name/* Indexed name */

on {[database_name. [Schema_name]. | Schema_name. ] Table_or_view_name}

(Column [ASC | DESC] [,... n])/* Index definition by */

eg

1 /* to create an index for the course Name column of the KCB table */ 2 Create Index Kc_name_ind 3      on KC (Cname) 4 Go

View Code

Result:picture2

eg

1 /* A unique clustered index is created from the course Number column of the KCB table because clustered is specified, so the index will physically sort the data on the disk.  */2createuniqueclusteredindex  xs_id_ind 3      on XS (Sname)

View Code

Result:

Picture3

eg

1 /* Create a composite index.  */2createindex  cj_ind3on      XS (sage,sno1 )

View Code

Result:

P4

eg

1 /* Create an index based on the total credits column in the XSB table */ 2 CREATE nonclustered INDEX  3on      XS (Sno1)

View Code

eg

1 /* Create a unique clustered index based on the XSB column in the table. If a duplicate key is entered, the INSERT or UPDATE statement is ignored.  */2CREATEUNIQUECLUSTEREDINDEX3 On      XS (Sno)

View Code

There are several points to create an index to illustrate:

(1) Create an index on a computed column. For a unique or primary key index, a computed column can be included as long as the index condition is satisfied, but the computed column must be deterministic and must be precise. If there is a function in the computed column, then the same parameter input is used when using the function, and the result of the output must be the same, and the computed column is determined. While some functions, such as getdate (), output different results each time they are called, you cannot define an index on a computed column.

It is also not possible to create an index on a column when it is computed as a text, ntext, or image column.

(2) Create an index on the view. You can define an index on a view. Indexed views are a way to store view result sets in a database, reducing the overhead of dynamically generating result sets. Indexed views can also automatically reflect changes made to the base table data after the index is created.

eg

1 /*defines the view, using the WITH SCHEMABINDING clause in the following example, so when defining the view, the table name in the SELECT clause must be in the form "schema name. Table Name"*/2 CREATE VIEWView_stu withSCHEMABINDING3      as 4     SELECTSno,sname5          fromdbo. XS6 GO7 8 /*Create an index on a view*/9 CREATE UNIQUE CLUSTERED INDEXInx1Ten      onView_stu (Sno) One GO

View Code

Result:

p5.1,p5.2

1. To delete an index by means of a graphical interface

The main steps to remove an index from a graphical interface are as follows:

Start SQL Server Management Studio, expand the Database pxscj→ table →dbo.xsb→ Index in Object Explorer, select the index you want to delete, right-click, and choose the Delete menu item on the shortcut menu that pops up. Click the OK button in the Delete Objects window that opens.

2. To delete an index from a SQL command

Deletes one or more indexes from the current database.

Syntax format:

DROP INDEX

{index_name on Table_or_view_name [,... N]

| Table_or_view_name.index_name [,... N]

}

The DROP INDEX statement can delete one or more indexes at a time. This statement is not suitable for deleting an index created by defining a primary key or a unique constraint. To delete an index created by a primary key or a unique constraint, you must implement it by deleting the constraint.

In addition, the drop index operation cannot be performed on the index of the system table.

eg

1 /* deletes an index named Kc_name_ind for the table KCB in the PXSCJ database.  */2IFEXISTS (SELECTfromWHERE= ' Kc_name_ind ' )3     DROPINDEX kc.kc_name_ind

View Code

sql--Index