Member Avatar for firoz.raj

what type of indexes exists in Sql Server ?which one is the fastest and why can we only have one of those per table ?

  • 3 Contributors
  • 2 Replies
  • 100 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sknake

Dani AI

Generated

Short answer for : SQL Server supports several index kinds — the common ones are clustered and nonclustered (with optional included columns), unique, filtered, columnstore, XML, spatial and full‑text indexes, plus hash/nonclustered indexes for memory‑optimized tables. Which is "fastest" depends entirely on the workload and the query pattern.

There is no single fastest index. For single‑row equality lookups a narrow, highly selective nonclustered index (or a memory‑optimized hash index for in‑memory tables) is usually best. For ordered range scans the clustered index (or a suitably keyed nonclustered index) shines. For large aggregations and analytics, columnstore indexes are often far faster because of columnar storage and compression. Each index type trades read speed for storage and write overhead.

To expand on : the clustered index determines physical storage, so its key choice affects insert/update performance and fragmentation. Common mitigations for heavy insert activity are choosing an ever‑increasing clustered key (IDENTITY), adjusting fillfactor to reduce page splits, using appropriate index maintenance (reorganize/rebuild), and creating targeted nonclustered or filtered indexes so read queries remain fast without over‑indexing writes. Also prefer narrow, stable keys and use included columns to create covering indexes instead of widening the key.

Practical next steps: measure with the execution plan and DMVs (sys.dm_db_index_usage_stats, sys.dm_db_index_physical_stats, sys.dm_db_missing_index_details) to find unused or missing indexes, test candidate indexes on representative data, and balance read vs write costs. ’s pointer is a useful starting read, but real tuning always requires profiling the actual workload.

Recommended Answers

All 2 Replies

Member Avatar for darkagn

Check out this great article by Alexander Chigrik. His explanations of indexes and their function in MSSQL is great, and he gives some really good insight into the optimisation of your database.

Member Avatar for sknake

firoz -- "fastest" isn't always best. The only type of index limited to one per table is a clustered index. The table's data is physically stored in order of the clustered index. Now image this for example: You have an "Invoice" table with "CreateDate" column and have a clustered index against that column in descending order. Now your searches based on the CreateDate column will be fast, the inserts will be slow. When you insert a new record it will have a newer createdate and have to physically shove all of the records down a notch to write at the top portion of the file descriptor, giving you very slow inserts. Everything in computing is a cost-benefit tradeoff, indexes are no exception.

Why don't you explain what you're trying to accomplish or learn?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.