8.2.1 creates an index with the CREATE INDEX command
CREATE index either creates a clustered index that alters the physical order of the table or creates a nonclustered index that improves query performance. The syntax is as follows:
CREATE [UNIQUE] [CLUSTERED | Nonclustered]
INDEX index_name on {table | view} column [ASC | DESC] [,... n])
[with
[Pad_index]
[[,] FILLFACTOR = FILLFACTOR]
[[,] ignore_dup_key]
[[,] drop_existing]
[[,] statistics_norecompute]
[[,] sort_in_tempdb]
]
[on filegroup]
The
parameters are described as follows:
The
- unique
Creates a unique index, where the key values of the index are not duplicated. You cannot build a unique index when a column contains duplicate values. To use this option, you should determine that the indexes contain columns that do not allow null values, or you will often make errors when you use them. The
- CLUSTERED
Indicates that the index created is a clustered index. If this option is default, the index created is not a clustered index. The
- nonclustered
indicates that the index created is not a clustered index. The data page contains pointers to the actual table data pages in the database. The
- index_name
Specifies the name of the index that is created. Index names should be unique in a table, but can be duplicated in the same database or in different databases. The
- table
Specifies the name of the table in which the index is created. You should also indicate the database name and owner name, if necessary. The
- View
Specifies the name of the view in which the index is created. The view must be defined using the SCHEMABINDING option, and its specific information is shown in the "View Creation" section.
- ASC | DESC The
specifies how a particular indexed column is sorted. The default value is ascending (ASC). The
- column
Specifies the columns to be indexed. If you use two or more columns to make up an index, it is called a composite index. You can specify up to 16 columns in an index, but the data type of the column cannot be longer than 900 bytes. The
- pad_index
Specifies the number of rows for the internal node that fills the index, at least greater than or equal to two rows. The PAD_INDEX option does not work until the FILLFACTOR option is specified. Because Pad_index uses the same percentage as fillfactor. By default, SQL Server ensures that each index page has no free space to hold a maximum index row of data. If fillfactor specifies a percentage that is not enough to hold a row of data s,ql Server automatically changes the percentage internally. The
- FILLFACTOR = FILLFACTOR
FILLFACTOR is called the fill factor, which specifies the percentage of the index page size for each index page when the index is created, and the FILLFACTOR value is 1 to 100. It also points to the percentage of free space that the index page retains as the index page size. namely 100-fillfactor. For those tables that frequently insert or delete large amounts of data, you should reserve a larger space for future indexed data, which will be fillfactorSmaller, the index pages will fill up quickly with the insertion of the data, resulting in paging, which can greatly increase the overhead of the system. However, if set too small, it will waste a lot of disk space, reduce query performance. Therefore, a fillfactor of approximately 10 is usually set for such a table. For data that does not change, high concurrency, read-only tables, the FILLFACTOR can be set to more than 95 or even 100.
If this option is not specified, SQL Server defaults to 0. 0 is a special value, unlike other small fillfactor
values (such as: 1,2), whose leaf-node pages are fully filled, and there is some space in the index page. You can
use stored procedure sp_configure to change the default FILLFACTOR value.
- ignore_dup_key
This option controls the response of SQL Server when you insert duplicate data into a column that is contained in a unique constraint. When this option is selected, SQL Server returns an error message, skips the insertion of this row of data, and continues to insert the following data: When this option is not selected, SQL Server not only returns an error message, but also rolls back the entire insert (ROLLS) Statement (for rollback, see the "Transactions" section of the "Database Update" chapter). The
- drop_existing
Specifies that you want to delete and re-create the clustered index. Deleting a clustered index causes all nonclustered indexes to be rebuilt because a row pointer is needed to replace the cluster index keys. If the cluster index is rebuilt, then the nonclustered index is rebuilt again to replace the row pointer with a clustered index key. Use the DROP_EXISTING option to rebuild a nonclustered index only once. The
- Statistics_norecompute
Specifies that distribution statistics are not automatically updated. You need to manually execute the updatestatistics command without the NORECOMPUTE clause. The
- sort_in_tempdb
Specifies that the sorting results that are used to create the index are stored in the TEMPDB database. If the tempdb database and the user database are on different disk devices, using this option reduces the time it takes to create an index, but it increases the amount of disk space required to create the index. The
- on filegroup
Specifies the filegroup in which the index resides.
Note: A column with a data type of text, NTEXT, image, or bit cannot be an indexed column.
because the width of the index cannot exceed 900 bytes, the column widths of the columns with char, VARCHAR, binary, and varbinary are more than 900 bytes, or the data type is nchar, Nvarchar columns cannot be indexed when the column width exceeds 450 bytes.
When you create an index using the Index Creation wizard, you cannot include computed columns in the index, but when you create an index by creating or using the CREATE INDEX command, you can create an index on the computer column, which is not allowed in previous versions of SQL Server2000 and is a major improvement.
Example 8-1: Create a clustered index for the table products.
create unique clustered index pk_p_id
on Products (p_id)
with
Pad_index,
fillfactor = 10,
Ignore_dup_key,
drop_existing,
Statistics_norecompute
on [primary]
Example 8-2 creates a composite index for table products
CREATE INDEX Pk_p_main
on Products (p_id, P_name, Sumvalue)--where sumvalue is a computed column expression for price*quantity
with
Pad_index,
fillfactor = 50
on [primary]
Example 8-3 creates a view and builds an index for it
CREATE VIEW Dbo.work_years
with
schemabinding
as
Select top percent emp_id,e_name, birthday, hire_date, Year (GETDATE ())
-year (hire_date) as Work_years
from Dbo.employee
ORDER BY work_years Desc
Create unique clustered
index Emp_id_view on Dbo.work_years (emp_id)
8.2.2 creates an index with Enterprise Manager.
There are two ways to create an index in Enterprise Manager
1 Creating an index with the Index Creation Wizard
creation method is as follows:
(1) In the directory tree, select the database that contains the table where you want to create the index, and click the wizard page in the taskpad
"Wizards", the Selection wizard interface appears as shown in Figure 8-2.
Figure 8-2 Select the Index Creation Wizard
(2) Click the "Create an index" link in the taskpad to show the Index Creation Wizard interface as shown in Figure 8-3. You can also choose the Izards menu item from the Tools menu, the Select SQL Server Wizard dialog box shown in Figure 5-17, and then select the Create Index Wizard option under "Database" from the tree directory will also appear as shown in Figure 8-3 The Index Creation wizard interface shown.
(3) Click the Next button to appear as shown in Figure 8-4, from which you can select the table to which you want to create the index, and the database to which you belong.
(4) Click the Next button and appear as shown in Figure 8-5, which displays information about the indexes that already exist in the selected table. If the table has not yet created an index, the interface does not appear, and the interface shown in Figure 8-6 appears directly.
(5) Click the Next button and appear as shown in Figure 8-6, which lists information about all the columns in the table, from which you select the columns to create the index.
(6) Click the Next button and appear as shown in Figure 8-7, which lists the index settings options you can select (for the setting of index options, refer to the CREATE INDEX command described earlier).
(7) Click the Next button to have the completion index creation interface as shown in Figure 8-8, where you can specify the name of the index you are creating, and you can adjust the order of the columns that make up the index. Click the Finish button to end the index creation process. A CREATE Index Success message dialog box pops up.
2 Creating indexes directly
Select the table you want to create an index, right-click, and select the Manage Indexes option from the All Tasks submenu from the shortcut menu, and the Index Management dialog box, shown in Figure 8-9, lists the indexes that already exist in the table. Select the "New" button to enter the Create Index dialog box as shown in Figure 8-10. In Figure 8-10, enter the name of the index you want to create, select the column to use to create the index, and set various options for indexing (for the setting of index options, refer to the CREATE INDEX command described earlier). Click the OK button to complete the creation of the index.
