SQL: which columns should have an index
In SQL tables might have primary keys and indexes. The primary key identifies each record. The key must be unique, it can't be NULL and each table must have just one key.
Indexes are something different: they are used to speed up the queries on a specific table. That means we absolutely need indexes on large tables which we often query.
However there's something we need to take in account: indexed tables are slower to update because the index itself need to be updated as well. Thus, we need to carefully decide which are the columns we want to be indexed.
Deciding which columns should be indexed is not that difficult. Consider your frequently used queries and create index for those columns that are used in WHERE clauses. Obviously it's a general idea, because if we query a table to get 10% to 20% of the contained records, an index might be useless.
I suggest to use 1 or 2 indexes in a table: too many of them will definitely slow down your updates.
Generally indexes speed up and optimise queries against a table. However we should not over use them. In that regard, there is an interesting tool I found on the net which will analyse your database and determine possible improvements by suggesting indexes. It's call Automated Auto-Indexing and it's worth giving it a try.
We can get all the already created indexes in a database by querying the sys.indexes and the sys.index_columns system tables:
Indexes are something different: they are used to speed up the queries on a specific table. That means we absolutely need indexes on large tables which we often query.
However there's something we need to take in account: indexed tables are slower to update because the index itself need to be updated as well. Thus, we need to carefully decide which are the columns we want to be indexed.
Deciding which columns should be indexed is not that difficult. Consider your frequently used queries and create index for those columns that are used in WHERE clauses. Obviously it's a general idea, because if we query a table to get 10% to 20% of the contained records, an index might be useless.
I suggest to use 1 or 2 indexes in a table: too many of them will definitely slow down your updates.
Generally indexes speed up and optimise queries against a table. However we should not over use them. In that regard, there is an interesting tool I found on the net which will analyse your database and determine possible improvements by suggesting indexes. It's call Automated Auto-Indexing and it's worth giving it a try.
We can get all the already created indexes in a database by querying the sys.indexes and the sys.index_columns system tables:
select * from sys.indexes
select * from sys.index_columns