Indexes in SQL Server

Debashish Chakraborty

11y1.1k

When and why should we use Indexes?? What are indexes basically?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajeesh Menoth11y ago
Hi,

Article in csharp corners:

1) http://www.c-sharpcorner.com/UploadFile/f0b2ed/index-in-sql/

Ref this following articles :

1) https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/

2) http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server

3) http://www.sqlteam.com/article/sql-server-indexes-the-basics

4) http://www.aspdotnet-suresh.com/2011/07/sql-server-indexes-tutorial-different.html
Santhakumar Munuswamy11y ago
Hi,

Refer with practical

http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server
Manoj Bhoir11y ago
Hi,

An index is used to speed up searching in the database.

MySQL have some good documentation on the subject (which is relevant for other SQL servers as well) http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

In MySQL you can use EXPLAIN in front of your SELECT statement to see if your query will make use of any index. This is a good start for troubleshooting performance problems. Read more here: http://dev.mysql.com/doc/refman/5.0/en/explain.html

For more details please check these links :

http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server
https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/
Manas Mohapatra11y ago
Hi Deb,

Index is very good concept in SqlServer. Index is nothing but database objects which helps to search records faster in tables. It also helps in performance improvement.

Suppose if you 10-15 lakhs records in your table and you are performing record search. The database engine will check all records to satisfy your condition which leads slow performance. But if we implement index here then database engine will perform search in index table instead of searching 10-15 lakh records. Index internally implements B-tree(Binary tree).

SQL Server offers two types of indexes clustered and non-clustered. In its simplest definition a clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes.

You can also create other types indexes in SQL server. Please follow below link
https://msdn.microsoft.com/en-us/library/ms175049.aspx

For more on Indexes please visit
http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server