SQL Server statement-create an index

Syntax:
Create [index type] Index name
On Table Name (column name)
With fillfactor = fill factor value 0 ~ 100
Go

/* Instance */
Use Database Name
Go
If exists (select * From sysindexes where name = 'ix _ test_tname ') -- check whether 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 -- create a non-clustered Index
On test (tname) -- creates 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 to query by 'ix _ test_tname 'Index

Summary:
1. What is index: the index in the database is a logical pointer list of a set of values in one or more columns in a table and corresponding data pages pointing to the values physically identified in the table.
2. Category:
Unique index: two rows cannot have the same index value (a unique constraint is created, and the system automatically creates a unique index)
Primary Key Index: each value in the primary key must be unique (a primary key index is automatically created when a primary key is created)
Clustered index: the physical order of each row in the table is the same as the logic (INDEX) Order of the key value. The table can only contain one clustered index, and the primary key column is a clustered index by default.
Nonclustered: The physical order of each row in the table does not match the logic (INDEX) Order of the key value. The table can have 249 non-clustered indexes.
3. Criteria for creating indexes: columns frequently searched by term; columns sorted by term
Note: if there are only a few rows in the table or the column contains only a few different values, it is not recommended to create an index, because SQL Server takes longer to search data by index in small tables than to search by row.

 

Create index (SQL Server compact Edition)
Http://msdn.microsoft.com/zh-cn/library/ms345331 (SQL .90). aspx

New:April 14, 2006

Create an index on the specified table. You can create an index before entering data in the table.

Syntax

CREATE [UNIQUE] [NONCLUSTERED] INDEX index_name ON table_name (column_name [ASC|DESC][,…n]) WITH (STATISTICS_NORECOMPUTE = { ON | OFF })]

Parameters

Terms	Definition
Unique	Create 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. After creating an index, SQL Server 2005 compact edition checks whether duplicate values exist (if data already exists ), this check is performed every time you use the insert or update statement to add data. You must first remove duplicate values before creating a unique index for the column. If duplicate key values exist, the create index statement is canceled and an error is returned. You can only create a unique index for a column defined as not null. If a unique index exists, the update or insert statement that generates duplicate key values will be rolled back, and SQL Server compact edition will return an error. Even if the update or insert statement changes many rows, the above is true if there is a duplicate.
Nonclustered	Creates an index for the logical sorting of a specified table. With non-clustered indexes, the physical order of data rows is independent of the index order. This is the only supported index type. (Default value: nonclustered)
Index_name	Specify the index name. The index name must be unique in the table, but not in the database.
Table_name	Name of the table to which the index is to be created. This table contains one or more columns to be indexed.
Column name	The column to which the index is applied. Specify the names of two or more columns to create a composite index for the composite values in the specified column. In the brackets behind the table, columns to be included in the composite index are listed in order of sorting priority. Note: You cannot specify a column that contains the ntext or image data type as the column to be indexed.
ASC | DESC]	Determines the ascending or descending order (DSC) direction for a specific index column. The default value is ASC.
N	It indicates that multiple column placeholders can be specified for any specific index. The maximum number of columns that an index can contain is 16.
Statistics_norecompute	Specifies whether to recalculate distribution statistics. 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 execute update statistics without the norecompute clause. Important: Disabling automatic re-calculation of distribution statistics may prevent the query optimizer from selecting the best execution plan for queries involving this table.

Example

The following example creates a unique index for the mymers MERs table:

Copy code

CREATE TABLE MyCustomers (CustID int, CompanyName nvarchar(50)) CREATE UNIQUE INDEX idxCustId ON MyCustomers (CustId)