Indexes in SQL Server

Question

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

Manoj Bhoir · Answer

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/

Rajeesh Menoth · Answer

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 Munuswamy · Answer

Hi, Refer with practical http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server

Manas Mohapatra · Answer

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