CODEWITHSUNDEEP
sql-serversql-server-2008
Devid Bun
Devid Bun

Reputation: 29

How to know on which column we have to apply index on table in SQL Server?

Suppose we have table that contains 100 columns. I want to apply index on that table, how can I determine those columns we have to apply an index?

Upvotes: 1

Views: 2520

Answers (3)

juergen d
juergen d

Reputation: 204894

First of all if you have a table with so many columns then you are probably doing something wrong.

But besides that look into your queries and as a rule of thumb you index the columns that you use in your where and order by clauses of the queries.

All infos the database needs to find or order a record should be indexed. Generally speaking.

For instance 

select top 10 name, description 
from your_table
where status = 1 
order by date desc

For this query the 2 columns status and date should be indexed.

Upvotes: 2

Meow Meow
Meow Meow

Reputation: 666

This query show missing indexes that should be created using a SQL server internal statistics. Also, analyze one more time every index which you want to create. Don't create more than 5-10 indexes per table.

SELECT TOP 25
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
    + CASE
    WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
    IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC

Author blog: https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

Upvotes: 0

Sumit Agarwal
Sumit Agarwal

Reputation: 1

Creating a Index on table depends on which columns you want to put WHERE clause.

Once you are able to know columns to use for WHERE clause you can create Index over them.

Typically numeric columns are good choice for Indexing.

Upvotes: 0

Related Questions