SQL Server Create and use indexes

To create an index:
(1) in SQL Server Management Studio, select and right-click the table to which you want to create an index, and choose design from the pop-up menu to open the Table Designer. Right-click the Table Designer and choose Indexes/keys from the pop-up menu to open the Indexes/Keys dialog box. The dialog box lists the indexes that already exist, as shown in.
(2) Click the "Add" button. In the selected primary/Unique key or index box, displays the name of the system that is assigned to the new index.
(3) Under the Column property, select the column to create the index. You can select up to 16 columns. For best performance, it is best to select only one or two columns. For each column you select, you can indicate whether the index organizes column values in ascending or descending order.
(4) If you want to create a unique index, select Yes in the is unique attribute.
(5) When the settings are complete, click the OK button.
(6) When the table is saved, the index is created in the database.
To create an index with a CREATE INDEX statement:

copy content to clipboardCode: CREATE[ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX索引名
    ON {表名|视图名} (列名[ ASC | DESC ] [ ,...n ] )

Cases:
To create a unique index ix_employees based on Idcard columns for table Employees in database Hrsystem, you can use the following command:

copy content to clipboardCode: USE HrSystem
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Employees]ONdbo.Employees(IdCard)
GO

Cases:
To create a unique, clustered index ix_employees1 based on column idcard for table employees, you can use the following command:

copy content to clipboardCode: USE HrSystem
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Employees1] ON [dbo].[Employees](IdCard)
GO

It is important to note that only one clustered index is allowed in a table. Therefore, if a clustered index already exists in table employees, the following error message will be prompted when the above statement is executed.
Msg 1902, Level 16, State 3, line 1th
Unable to table ' dbo '. Employees ' creates multiple clustered indexes. Please delete the existing clustered index ' pk__employee__263e2dd300551192 ' before creating a new clustered index. Cases:
You can use the following command to create an index on a Table employees column Emp_name in descending order:

copy content to clipboardCode: USE HrSystem
GO
CREATENONCLUSTERED INDEX [IX_Employees2] ON [dbo].[Employees]
(
  [Emp_name] DESC
)
GO

Using the INCLUDE clause in the CREATE INDEX statement, you can define the included Nonkey columns when you create the index with the following syntax:

copy content to clipboardCode: CREATENONCLUSTERED  INDEX 索引名
    ON { 表名| 视图名 } ( 列名 [ ASC | DESC ] [ ,...n ] )
  INCLUDE (<列名1>, <列名2>, [,… n])

Example: Create a nonclustered index on table employees ix_wage, the keys in the index are listed as wage, non-key columns are emp_name, sex, and title, as follows:

copy content to clipboardCode: USEHrSystem
GO
CREATENONCLUSTERED  INDEX IX_Wage
  ON Employees ( Wage )
  INCLUDE (Emp_name, Sex, Title)
GO

Example: After creating an index Ix_wage, executing the following SELECT statement will significantly improve query efficiency when the amount of data in the table employees is large.

copy content to clipboardCode: USEHrSystem
GO
SELECTEmp_name, Sex, Title, Wage
FROMEmployees
WHEREWage BETWEEN 1000 AND 3000
GO

To modify an index:
In SQL Server Management Studio, select and right-click the table you want to create an index from, and choose Design Table from the pop-up menu to open the Table Designer. Right-click the Table Designer, choose Indexes/Keys from the pop-up menu, open the Indexes/Keys dialog box, and view the existing indexes and modify the index's property information.
You can also use the ALTER INDEX statement to modify the index with the following basic syntax:

copy content to clipboardCode: ALTER INDEX { 索引名| ALL }
    ON <表名|视图名>
    { REBUILD  | DISABLE  | REORGANIZE }[ ; ]

The ALTER index statement has more complex parameters, and it only describes its basic usage. The parameters are described as follows:
REBUILD specifies that the index be rebuilt. Disable specifies that the index is marked as disabled. REORGANIZE specifies the index leaf level that will be re-organized.
Cases:
To disable index ix_employees, you can use the following statement:

copy content to clipboardCode: USE HrSystem
GO
ALTERINDEX IX_Employees ON Employees DISABLE
GO

To delete an index:
In SQL Server Management Studio, select and right-click the table you want to create an index from, and choose Design Table from the pop-up menu. Open the Table Designer. Right-click the Table Designer and select the index/Key command from the pop-up menu to list the indexes that already exist in the Index/Key dialog box that opens. Click the Delete button to delete the index information.

copy content to clipboardCode: DROP INDEX 表名.索引名|视图名.索引名[ ,...n ] Summary:

O (1) not as many indexes as possible, if there is a large number of indexes in a table, it not only consumes a lot of disk space, but also affects the performance of the Insert, DELETE, UPDATE and other statements. The index is also adjusted and updated as the data in the table changes.
O (2) Avoid too many indexes on frequently updated tables, and as few columns as possible in the index. For fields that are frequently used for queries, you should create an index, but avoid adding unnecessary fields.
O (3) a table with a small amount of data is best not to use an index, and because there is less data, the query may take less time than traversing the index, and the index may not have an optimization effect.
O (4) Build indexes on columns that are often used in conditional expressions with more different values, and do not index on columns with fewer values. For example, in the "Gender" field of the student table there are only two different values for "male" and "female", so there is no need to index. If the index is indexed, it will not only improve the efficiency of the query, but greatly reduce the update speed.
O (5) specifies a unique index when uniqueness is a feature of the data itself. Using a unique index ensures the data integrity of the defined columns and improves query speed.
O (6) Index on columns that are frequently sorted or grouped (that is, groupby or order by), and if there are multiple columns to be sorted, a composite index can be established on those columns. http://bbs.51cto.com/viewthread.php?tid=1144146&extra=&page=2http://blog.csdn.net/wltica/article/details/8537440

SQL Server Create and use indexes