1- Not based on Table design, based on load
As soon as creating table, you don’t need to create whole indexes. Observing loads and managing indexes would be better.
2- Size of Table boyutları
Most of the operating systems read data from disk with a 64KB packages. So if your table is small (lets say less than 1000 rows) you may need an index. Additionally creating new index may slower your queries. Because sql server should read index pages first and then it reads data pages. So it means 2 read actions.
3- Execution Count of SQL Scripts
It is not good that having a table with tens of indexes. So try to minimize index counts to get rid of heavy loads. Some of your reports may run slower if other applications are ok with it (or you report doesn’t belongs to your CEO)
The higher cardinality the better performance you will have. For example creating index on status field or something like flag, sex etc. may not limit your reading size. You can join more than a column and create unified indexed column to gain more cardinality.
5- Not Only “Where” part, “Select” parts are also Important
SELECT name, sirname, city FROM Table_name
WHERE name = ‘omer’ and sirname = ‘Zeybek’
Let’s assume that you have an index on name and sirname columns, SQL Server can find the specified record and read directly from disk. However, in the SELECT part you want to browse city, too. So SQL SERVER should access again to read city. For small data returns, effect of this query may not sense, but if your query is requesting thousands of records, SQL SERVER must read all records twice and blocks more columns than needed.
6- Updating Indexed Columns
Any updates, deletes and inserts on a table also performed on index pages if update columns are part of index. For example, you have an index on update_date column and your application is updating simultaneously this column. SQL SERVER should update index pages after each updates on update_date column. It causes defragmentation on index pages and also decrease the performance of your updates.
7- Sort and Order By
Sorting and order by operations are performed by CPU. It you have not any problem on table, index size and counts and CPU usage is your only problem to concern, than you may create an index to help your CPU.
8- Creating Indexes on Different Disks
If you have GB level database and need more and more performance but you have done everything, than you can separate your indexes and data on to separated disks. It helps your reads and performs parallel. But only non-clustered indexes must be separated, clustered indexes must be located in same drive with data since clustered indexed contains data not addresses of data.