Check how many index present on Table
End Result would be like
Composite Index
- Has Same Syntax as Clustered and Non-Clustered, the only difference is that it using/including two or more columns.
- Disadvantage
- The more number of columns you choose to index, the longer it takes to create an index.
- For example our People table has 50 millions of records. if we are going to create a separate entity on those two columns with 50 million rows also. And then it's going to have pointers or page numbers illustrating the last name and first name. This is going to take about four minutes to create composite index on this table. '
What are disadvantage/ not recommended of more indexes on a single table
- Each non clustered index will take/ going to need extra space. if your table is taking around 20 GB and indexing almost occupy same to almost double your spaces i.e. 40 GB
Factors while creating indexes (whether we need indexes or not)
- When there is large amounts of data,
- If you're not using a table that has 100 million rows or 200 million rows or a billion rows, then there's no need to use an index on that table
- When table is user frequently
- When column is frequently used in WHERE clause
- Columns that are frequently referenced in ORDER BY or GROUP BY clauses
- index should be used on columns having high number of unique values
- Create a single column index when WHERE clause has as a single column
- Create a Multiple columns index when WHERE clause has as a multiple columns
- Build index on column of integer type. Integer is taking less space to store.
When should index be avoided
- Index should not be used on small table
- Index should not be used on a column that contain high number of NULL values
- Don't use index that will return high percentage of data
- if there only few possibility in huge data in a column like gender (having 2-3 options)