Code Free World

How do I index a database column


Hopefully, I can get answers for each database server.

For an outline of how indexing works check out: http://stackoverflow.com/questions/1108/how-does-database-indexing-work

The following is SQL92 standard so should be supported by the majority of RDMBS that use SQL:

CREATE INDEX [index name] ON [table name] ( [column name] )

Sql Server 2005 gives you the ability to specify a covering index. This is an index that includes data from other columns at the leaf level, so you don't have to go back to the table to get columns that aren't included in the index keys.

create nonclustered index myidx on mytable (mycol1 asc, mycol2 asc) include (my_col3);

This is invaluable for a query that has mycol3 in the select list, and mycol1 and my_col2 in the where clause.

For python pytables, indexes don't have names and they are bound to single columns:

tables.columns.column_name.createIndex()

In SQL Server, you can do the following: (MSDN Link to full list of options.)

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]

(ignoring some more advanced options...)

The name of each Index must be unique database wide.

All indexes can have multiple columns, and each column can be ordered in whatever order you want.

Clustered indexes are unique - one per table. They can't have INCLUDEd columns.

Nonclustered indexes are not unique, and can have up to 999 per table. They can have included columns, and where clauses.

To create indexes following stuff can be used:

  1. Creates an index on a table. Duplicate values are allowed:

    CREATE INDEX index_name ON table_name (column_name)

  2. Creates a unique index on a table. Duplicate values are not allowed:

    CREATE UNIQUE INDEX index_name ON table_name (column_name)

  3. Clustered Index

    CREATE CLUSTERED INDEX CL_ID ON SALES(ID);

  4. Non-clustered index

    CREATE NONCLUSTERED INDEX NONCI_PC ON SALES(ProductCode);

refer http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server for details.