SQL CREATE INDEX, syntax

--unique Unique index, clustered clustered index, nonclustered nonclustered index. The primary key is unique, so a primary key is created and a unique index is created for that field. SQL Server defines the primary key as a clustered index by default, in fact, whether the index is unique or not, the clustered index can be a unique index, or it can be a non-unique index; The unique index is actually required that all the data in the specified column must be different/* The difference between the primary key and the unique index:         1 A table's primary key can have only one, and a unique index can build multiple.         the 2 primary key can be used as a foreign key for other tables.         3 The primary key is not nullable, and the unique index can be null. Clustered index: A directory in which data within a table is arranged according to certain rules. Because of this, there is only one focus index in a table. To this we should note that the "primary key is the focus index" is extremely wrong, is a focus on the index of a waste. (although the SQL Server default primary key is the focused index) the biggest benefit of using a focused index is to quickly narrow the query to the query requirements and avoid full table scans. Next, a field with a different number of fields as a focused index does not conform to the principle that a clustered index should not be established under a large number of cases. */use Salesif (Exists (SELECT * from sys.indexes where name= ' Ix_test_tname ')) DROP index Tx_test_tname  --If Ix_test_ Tname exists delete the index create nonclustered index  ix_test_tname  --Create a nonclustered index index on t_test (name)--for T_ The name field of the test table creates an index with fillfachor=30--a fill factor of 30%. You can omit the word with fillfachor=30, which is the default go

Unique index comparison with primary key index unique index The single index does not allow two rows to have the same index value. If duplicate key values exist in the existing data, most databases do not allow the newly created unique index to be saved with the table. The database also rejects this data when the new data duplicates the key values in the table. For example, if a unique index is created on the employee's last name (lname) column in the Employee table, all employees cannot have the same surname. Primary key index The primary key index is a special type of unique index. A database table typically has a column or combination of columns whose values are used to uniquely identify each row in the table. This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows for fast access to the data. Some comparisons: (1) for the main/unique constraint, Oracle/sql server/mysql and so on will automatically establish a unique index; (2) The primary key does not necessarily contain only one field, so if you Jianjian a unique index in one of the primary keys, it is necessary; 3) The primary health can be a foreign health, the only index is not; (4) The primary health cannot be empty, the unique index can be, (5) The main health is also a combination of multiple fields; (6) The primary key differs from the unique index: a. There is not a null attribute; b. There can only be one per table. 1. Primary key ID, primary key is both a constraint and an index, and a key value for object caching. 2, Index * combination or sub-table referencing the relationship (when the data is large), you need to establish a nonclustered index on the column of the associated primary table (such as the Product ID field in the order schedule, the Order ID field associated with the order schedule) * The size of the index key cannot exceed 900 bytes, when the size of the list exceeds 900 bytes       Or a number of columns and more than 900 bytes, the database will error. * A large number of indexes in the table will affect the performance of the insert, UPDATE, and DELETE statements because all indexes will have to be appropriately adjusted when the data in the table changes.       You need to avoid too many indexes on frequently updated tables, and the indexes should remain narrow, that is, columns should be as small as possible. * Create indexes for predicates that are frequently used for queries, such as code, name, and so on for drop-down reference quick lookups. The like conditional statement in the query SQL statement for the existing dropdown reference in the platform is changed to not have a pre-wildcard character.       There is also a need to focus on the index design of the order by and group by predicates, where the predicates for order by and group by are ordered, and in some cases the predicates for order by and group by are indexed, avoiding the sort action at query time. * Basic weight for contentComplex columns, such as only 1 and 0, prohibit indexing because the index is extremely selective, misleading the optimizer to make the wrong choice in certain circumstances, resulting in a greatly reduced query speed. * When an index has more than one column, you should be careful to put a strong, selective column in front.       In order to be different, there may be a magnitude difference in performance. * Indexing a small table may not produce an optimization effect, because the query optimizer spends more time traversing the indexes used to search for data than performing a simple table scan, and the size of the table needs to be considered when designing the index. Tables with a record number not greater than 100 do not make an index. Small number of frequently-operated tables is not recommended for indexing (no more than 5,000 records)

http://blog.csdn.net/buynider/article/details/6057609