I need some scenarios where indexing of different columns can help queries getting data faster,
Please let me know i any link you have stated the same in exemplar way, i do not want to learn indexing basics but try to do some real time scenarios.
I believe firstly you will need to understand how indexes works and how it can improve on queries. Once you know that, your question will be answered.
yes your are right and i also have some experience on indexing but i want to see some useful and tricky techniques where i can use most of the index.
It will be based on the requirement (or the query itself), if its a simple DB, having a cover index for the most heavy query should fulfill your needs.
In general, cluster index best use case will be for range scan, non-cluster will be for given value search. Ensure that the column you selected is not a true/false column, meaning it should have more different values in order for the index to be better utilize. The list goes on and on, but at least a starting point
hope this helps
You need to look, at a minimum, at the following stats that SQL captures:
missing index stats -- from views sys.dm_db_missing_index_*
index usage stats -- sys.dm_db_index_usage_stats
index operational stats -- sys.dm_db_index_operational_stats
Since these stats get lost/reset when SQL restarts, it's best if the server has been running continuously for at least 30 days prior to analyzing the stats.
By far the most critical thing is to get the best clustered index on every table. After that, you can deal with nonclustered index(es), if any. Note: an identity column is most often NOT the best clustered index on a majority of tables. Yes, seriously, for best performance, it is NOT identity clustering for a majority of tables.
The analysis is not "tricky" but it does involve judgment -- you will often have to make your own choices regarding the best compromises to make for creating and modifying indexes.