Developers can provide good & quick query response to users by following best practices and techniques during the development. Database Administrators (DBAs) and SQL Developers, many times get stuck with scripts, whether they are working on SQL Server, Oracle, MySQL, Postgres or DB2, that slow down query result and overall performance of a system. There are some good techniques, which when followed can yield positive results. One of which includes fully utilizing Indexes, Indexing is challenging, but helps to improve the performance of database query.

Here is a list of some useful tips that a SQL developer can follow while using Indexes:

• Before building an Index it is essential to understand the data, its type and how queries are being executed to fetch data.
  
  
• It is preferable to have shorter Indexes as they are faster, thus giving you more hits in Index Cache.
  
  
• Don't add the same Index again on a table having different names.
  
  
• It is better to create Indexes on columns with integer values rather than columns having characters as integer values use less overhead in comparison to character values.
  
  
• When creating Indexes, you need to determine the number of unique values the columns will have for a particular field.
  
  
• Some databases give good search results with unique and indexed columns. So it is good to index columns that are unique. Don't index a unique column if it has never been used for searching purposes.
  
  
• Indexes should be measured on all columns frequently used in WHERE, ORDER BY, GROUP BY, TOP and DISTINCT clauses.
  
  
• Composite Indexes should be created if you think of running queries with multiple fields in the WHERE clause and where all the fields together will give considerably less rows as compared to the first field alone.
  
  
• All tables should contain a clustered Index, generally on the Index key. However there can be only one clustered Index per table.
  
  
• The Index Column used for Indexing should be made short to make the identification of each row easier by the database.
  
  
• It is useful to create a highly selective Index i.e. when the ratio of the qualifying number of rows is lower than the total number of rows.
  
  
• The position of a column is also important for improving SQL query performance. For better performance, it is advisable to place most selective tables to the left in the key of a non-clustered Index.
  
  
• Covering Index will allow you to store the columns you repeatedly want to fetch "in" the Index, so that SQL server uses more effective Index Seek and doesn't use the inefficient Key Lookup to fetch the columns you want.
  
  
• When only prefix or postfix of the string can be Indexed, don't Index the whole string. This specially holds true when the prefix or postfix of the string gives a unique identifier for the string.
  
  
• Creating Index on a foreign key column can improve the join performance.
  
  
• It is not advisable to create Indexes to be used in join situations where joins cannot use Indexes for processing for example Indexing a LEFT table in a LEFT join.
  
  
• Dropping unused Indexes is helpful in increasing the speed of data modifications. You can temporarily disable Indexes to assist in mass data modification, than re-enable & rebuild later.
  
  

The above listed points will help a SQL Developer to use Indexing efficiently to get optimum results. However, to use the correct Indexes in database, you should conduct careful analysis and testing. These tips will be useful, but you should test them to make them work for you. You might come across many other ways to use Indexing, with your experience and knowledge of SQL queries. 

Semaphore Software specializes in custom software development services with a rich experience in providing ASP.NET, PHP, Cloud and other open source software development services. Contact us for any software development needs!!