1.4 SQL Server 2000 index managementSimilar to the index in books, the index in the database allows the database program to locate the required data without scanning the entire table. The index in the book is a word list, which indicates the page number of each word. The index in the database is a set of values of one or more columns in a table, and a logical pointer list pointing to the data page that physically identifies these values in the table.
1.4.1 index designIn SQL Server, there are two special indexes: unique index and clustered index. (1) in SQL Server, when uniqueness is a feature of data, you can create a unique index. However, the combination of index columns is different from the table's primary key. For example, if studentsinfo's student ID (stu_id) column is frequently queried and the student ID is unique, a unique index is created on stu_id. SQL Server 2000 automatically creates a unique index to enforce the uniqueness requirements of the primary key and unique constraints. Unless a clustered index exists in the table or a non-clustered index is explicitly specified, a unique clustered index is created to implement the primary key constraint. Unless a clustered index is explicitly specified, a unique non-clustered index is created by default to force the unique constraint. (2) clustered index determines the physical sequence of data in the table. The clustered index is similar to the phone book, which sorts data by last name. Because clustered indexes specify the physical storage sequence of data in a table, a table can only contain one clustered index. Clustered indexes are particularly effective for columns that frequently search for range values. For example, if you frequently query the student information of a first-year segment, you can use the clustered index to quickly find the row containing the start age, and then retrieve all adjacent rows in the table until the end age is reached. This helps improve the performance of such queries. When the index value is unique, it is efficient to use clustered indexes to find specific rows. For example, the quickest way to use student ID stu_id to find a specific student is to create a clustered index or primary key constraint on the stu_id column.
1.4.2 create, modify, and delete an indexIn the table Properties dialog box, you can use the index/key option card to create an index, as shown in Figure 4-19. Figure 4-19 create an index on the index/Key page. In this dialog box, click Create, the "selected index" text box displays the name assigned to the new index by the system. Under "column name", select the column for which you want to create an index. You can set the sorting method for the selected column by using the "order" column. To create a unique index, select the "create unique" check box. To create a clustered index, select the "Create as clustered" check box. Because a table can only have one clustered index, if other clustered indexes already exist in the index, the user will be prompted not to create a new clustered index. After setting, click "close. When a table or relational graph is saved, the index is created in the database. You can also use the create index statement in the transact-SQL statement. Use the create index command in the transact-SQL statement to create an index. The syntax format is as follows: Create [unique] [clustered | nonclustered] Index
Index_nameOn {
Table|
View}(
Column[ASC | DESC] [,... n]) [with [pad_index] [[,] fillfactor = fillfactor] [[,] ignore_dup_key] [[,] drop_existing] [,] statistics_norecompute] [[,] sort_in_tempdb] [ON filegrou
P] The parameters are described as follows. ● Unique: used to create a unique index for a table or view. That is, two rows with the same index value are not allowed. ● Clustered: used to specify the created index as a clustered index. ● Nonclustered: used to specify the created index as a non-clustered index. ●
Index_name
:Specifies the name of the created index. ●
Table
:The name of the table used to create the index. ●
View
:Specifies the name of the view for which an index is created. ●
ASC| Desc: used to specify the ascending or descending order direction of a specific index column. ●
Column
:Used to specify the column to be indexed. ● Pad_index: used to specify the open space for each page (node) at the intermediate level of the index. ● Fillfactor = fillfactor: used to specify the percentage of data on each index page during index creation. The value of fillfactor is 1 to 100. ● Ignore_dup_key: used to control the SQL server response when duplicate data is inserted into a column that contains a unique clustered index. ● Drop_existing: used to specify whether to delete and recreate the named existing clustered index or non-clustered index. ● Statistics_norecompute: used to specify the expired index statistics and will not be automatically recalculated. ● Sort_in_tempdb: used to specify that the intermediate sorting results when an index is created are stored in the tempdb database. ● On filegroup: Specifies the file group for storing indexes. For example, create a unique index for the studentsinfo column. Use studentcreate unique index ix_studentsinfoon studentsinfo (stu_id) in the "index/Key" dialog box of the "Table properties" dialog box, you can also modify and delete indexes. You can also use the drop index statement in the transact-SQL statement. The syntax format is as follows: drop index {table. index | view. index }[,... n] Where, table | view is used to specify the table or index view where the index column is located, and index is used to specify the name of the index to be deleted. For example, to delete the index named ix_studentsinfo in the studentsinfo table, the syntax is drop index studentsinfo. ix_studentsinfo Note: The drop index command cannot delete the primary key or uniqueness-constrained index created by the create table or alter table command, or delete the index in the system table.
