Create indexing with SQL
To index a table, start the ISQL/w program in the SQL Sever program group in the taskbar. After entering the query window, enter the following statement:
CREATE INDEX mycolumn_index ON mytable (myclumn)
This statement creates an index called mycolumn_index. You can give an inquiry to any name, but you should include the indexed field name in the index name, which will be helpful in figuring out the intention of creating that index in the future.
Notice:
When executing any SQL statement, you will receive the following message:
This command did not return data,and it did not return any rows
This means that the statement has been executed successfully.
Index mycolumn_index to perform the mycolumn field of the table mytable. This is a nonclustered index and a non-unique index. (This is the default property of an index)
If you need to change the type of an index, you must delete the original index and rebuild one. After creating an index, you can delete it with the following SQL statement:
DROP INDEX mytable.mycolumn_index
Note that you want to include the name of the table in the DROP INDEX statement. In this example, the index you deleted is mycolumn_index, which is the index of the table mytable.
To create a clustered index, you can use the keyword CLUSTERED. ) Remember that a table can only have one clustered index. (Here is an example of how to establish a clustered index on a table:
CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)
If there are duplicate records in the table, an error will appear when you try to index with this statement. But tables with duplicate records can also be indexed; you just use the keyword ALLOW_DUP_ROW to tell SQL Sever this:
CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)
WITH ALLOW_DUP_ROW
This statement creates a clustered index that allows duplicate records. You should try to avoid duplicate records in a table, but if it already appears, you can use this method.
To create a unique index on a table, you can use the keyword UNIQUE. This keyword can be used for both clustered and nonclustered indexes. Here is an example:
CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
This is the index creation statement you will use frequently. Whenever you can, you should try to establish a unique clustered index on a table to enhance query operations.
Finally, you need to create an index for multiple fields - a composite index - that contains multiple field names at the same time in the index creation statement. The following example indexes the firstname and lastname fields:
CREATE INDEX name_index ON username(firstname,lastname)
This example creates a single index for two fields. In a composite index, you can index up to 16 fields.
Previous page12Read the full text