How do I index a database field

Question

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

Answer 1

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] )

Answer 2

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.

Answer 3

This seems like a question that would be well answered by the docs for the database server you might be using. For Oracle:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm

Answer 4

@John Downey: I can't find CREATE INDEX in my copy of the SQL-92 spec, which isn't surprising because the standard doesn't make assumptions about physical storage.