SQL Azure Online Training | Azure SQL Training

1. SQL Server index | Best practices for SQL Server indexes Below are some of the best practices or points to consider when creating SQL Server indexes. 1. Although indexes can speed up querying of a database, they slow down changes to the database. The heavier a table is in writing, the more careful you should be when adding your indexes. 2. Keep the indexes as narrow as possible. In other words, use the absolute minimum number of columns needed to achieve the desired effect. The larger the composite index, the harder SQL Server will work to keep it up to date and use it in queries. 3. The first column you specify should be as unique as possible, and ideally the one used by most WHERE clauses in queries.

2. 4. Composite indexes which also cover indexes are always useful. These indexes are built from several columns and all the columns necessary to satisfy a query are included in the index, this is why the index is supposed to cover the query. 5. Avoid using composite indexes as the clustered index of a table. Clustered indexes do not work as well when they are based on multiple columns. Clustered indexes physically order the rows of data in the table and work best when based on a single column [Source: The final guide to scaling SQL Server 2005 Edition by Realtimepublishers.com] Some other common best practices when creating SQL Server indexes are: 1. Use clustered indexes on primary keys, by default. 2. Index on the search columns. 3. Use a fill factor at the database level. 4. Use a fill factor at the index level. 5. Index on foreign key columns. [Source: Practices Pro SQL Server 2012 by Chris Shaw, Grant Fritchey, Carlos Bossy, Jeremy Lowell, Gail Shaw, Jesper Johansen, Mladen Prajdi, Wendy Pastrick, Kellyn Pot'Vin, Jason Strate, Herve Roggero, TJay Belt, Jonathan Gardner, Glenn Berry, Bradley Ball, Jes Borland, Ben DeBow, Louis Davidson] Learn about best practices for SQL Server indexes The use of indexes by SQL Server depends on a number of factors, including, but not limited to: the construction of the query, the tables referenced in the query, the columns referenced, the number of rows in the table, data distribution and uniqueness of the data index column (s). Here are some basic guidelines to keep in mind when developing your indexing strategy: 1.Add indexes according to your requests for number of high priorities and high execution. Determine in advance the acceptable execution times for queries that can be based on your business needs.

3. 2.Do not add too many indexes at the same time. Instead, add an index and test the query to see that the new index is used. If not in use, remove it. If used, test to make sure there are no negative side effects on the other queries.. 3. Unless you have a very good reason not to, always add a clustered index to each table. 4.A table without a clustered index is a heap, which means that the data is not stored in any particular order. Clustered indexes are classified according to the clustered key and its data pages are reorganized during an index reconstruction or reorganization. 5.Monitor query performance over time. As your data and the activity of your applications evolve, the performance and efficiency of your indexes also change. 6. Fragmented indexes can slow query performance because more I / O operations are required to return the results of a query. Minimize the fragmentation of indexes by rebuilding and/or reorganizing your indexes on a planned basis or as needed. 7.Select clustered index keys that are rarely changed, highly unique, and narrow across the width of the data type. The width is particularly important because each nonclustered index also contains the clustered index key. 8.Nonclustered indexes are ideal for small or single-line result sets. Again, the columns must be chosen according to their use in a query, in particular in the JOIN or WHERE clause. Nonclustered indexes should be created on columns containing highly unique data. 9.Use a fill factor of 100% for indexes located in file groups or read-only databases. This reduces I / O and can improve query performance since fewer pages of data are required to populate the result set of a query. 10.Avoid large index keys. Always test narrower composite keys in favor of larger indexes. 11.Try to anticipate which indexes will be needed based on the queries you make, but don't be afraid to use the Database Engine Tuning Advisor tool frequently. For More Information about SQL Azure ClickHere Ph No: +91-9989971070, E-Mail ID: online@visualpath.in