Tags: Database Index , MSSQL

Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

As described above database index helps only for search operation. Insert, Update and Delete will suffer. Index should be created only selected fields based on your retrieval of data (Columns).

- Source (WikiPedia: http://en.wikipedia.org/wiki/Database_index)

Indexing is serious business for any large application or small application. Haphazard use of Indexing will create an overhead on database so be careful while dealing with index.

Today, There is nothing much to say. All I can say is you can bookmark this article for future use of these queries. It may help you to save your time rather then wasting time on Googling.

SHOW ALL INDEXES

/* Use `*` to check out other fields. */
SELECT distinct ind.name as IndexName,t.name as TableName
FROM 
sys.indexes ind 
INNER JOIN 
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN 
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
sys.tables t ON ind.object_id = t.object_id 
WHERE 
ind.is_primary_key = 0 
AND ind.is_unique_constraint = 0 
AND t.is_ms_shipped = 0
AND ind.is_unique IN(0, 1)

DROP ALL INDEXES "UNIQUE" and "NON UNIQUE"

DECLARE @query nvarchar(max);
SELECT @query = 
(SELECT distinct 'DROP INDEX '+ ind.name + ' ON ' + t.name +';'
FROM 
sys.indexes ind 
INNER JOIN 
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN 
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
sys.tables t ON ind.object_id = t.object_id 
WHERE 
ind.is_primary_key = 0  
AND ind.is_unique_constraint = 0 
AND t.is_ms_shipped = 0
AND ind.is_unique IN(0, 1)
for xml path(''));
EXEC sp_executesql @query 

Tags: Database Index , MSSQL