SQL Service Add index

Grammar:
CREATE [Index type] Index index name
On table name (column name)
With FILLFACTOR = fill factor value 0~100
GO

/* Instance */
Use library name
GO
If EXISTS (SELECT * from sysindexes WHERE name= ' ix_test_tname ')--detects if the Ix_test_tname index already exists
DROP INDEX TEST. ix_test_tname--Delete if it exists

--Create an index
Create nonclustered index Ix_test_tname--Creating a nonclustered index
On test (tname)--Create an index for the Tname field of the test table
With FILLFACTOR = 30--fill factor is 30%
GO

SELECT * from TEST (index = ix_test_tname) WHERE tname = ' A '--Specify press ' ix_test_tname ' index query

Summarize:
1. What is an index: an index in a database is a collection of one or more column values in a table and a logical pointer list corresponding to the data pages that physically identify those values in the table.
2. Index Type Classification :
Unique index (unique): does not allow two rows to have the same index value (creates a unique constraint and the system automatically creates a unique index)
Primary KEY index: Primary key index requires that each value in the primary key be unique, (create primary key to automatically create primary key index)
Clustered index (CLUSTERED): The physical order of the rows in the table is the same as the logical (indexed) Order of the key values, and the table can contain only one clustered index, and the primary key Lieme the clustered index
Nonclustered index (nonclustered): the physical order of the rows in the table does not match the logical (indexed) Order of the key values, which can have 249 nonclustered indexes in the table
3. Criteria for index creation: columns for frequently searched terms;
Note: If there are only a few rows in a table, or if the column contains only a few different values, it is not recommended to create an index because SQL Server spends more time searching for data in a small table than it does on a row-by-line basis.

CREATE INDEX (SQL Server Compact Edition)
http://msdn.microsoft.com/zh-cn/library/ms345331 (sql.90). aspx

Added: November 26, 2015

Creates an index on the specified table. You can create an index before you enter data in the table.

Grammar

CREATE [UNIQUE] [nonclustered] INDEX index_name on table_name (column_name [asc| desc][,... N]) With (Statistics_norecompute = {on | OFF})]

CREATE [UNIQUE] [nonclustered] INDEX index_name on table_name (column_name [asc| desc][,... N]) With (Statistics_norecompute = {on | OFF})]

Parameters

Terminology	definition
UNIQUE	Creates a unique index on the table. A unique index is an index that does not allow any two rows to have the same index value. SQL Server 2005 Compact Edition (SQL Server Compact Edition) checks for duplicate values after the index is created (if the data already exists) and performs the check every time the data is added using the INSERT or UPDATE statement Check the operation. Duplicate values must be eliminated before a unique index can be created on the column. If there are duplicate key values, the CREATE INDEX statement is canceled and an error is returned. You can only create a unique index on a column that is defined as not NULL. If there is a unique index, the UPDATE or INSERT statement that may generate duplicate key values is rolled back, and the SQL Server Compact Edition returns an error. This is true even if the UPDATE or INSERT statement changes many rows, but as long as there is a repetition.
Nonclustered	Creates an index of the logical sort for the specified table. With nonclustered indexes, the physical order of the data rows is independent of their index order. This is the only supported index type. (default value is nonclustered)
Index_name	Specifies the name of the index. The index name must be unique in the table, but it does not have to be unique in the database.
table_name	Specifies the name of the table on which you want to create an index. This table contains one or more columns to be indexed.
Column Name	The column to apply the index to. Specify the names of two or more columns to create a composite index of the combined values in the specified column. Two. In parentheses after the table, list the columns to be included in the combined index in order of precedence. Attention: You cannot specify columns that contain ntext or image data types as columns to be indexed.
ASC | DESC]	Determines the ascending (ASC) or descending (DSC) sort direction for a specific indexed column. The default value is ASC.
N	Indicates that a placeholder for multiple columns can be specified for any particular index. The maximum number of columns that can be contained in an index is 16.
Statistics_norecompute	Specifies whether distribution statistics are recalculated. The default value is OFF. On Expired statistics are not automatically recalculated. OFF Enable automatic statistics update To restore automatic statistics updates, set Statistics_norecompute to OFF, or perform an update STATISTICS without the NORECOMPUTE clause. Important matters: Disabling the automatic recalculation of distribution statistics may prevent the query optimizer from picking the best execution plan for queries that involve this table.

Example

The following example creates a unique index on the mycustomers table:

CREATE TABLE mycustomers (CustID int, CompanyName nvarchar (50)) CREATE UNIQUE INDEX Idxcustid on mycustomers (CustId)

SQL Service Add index