Answer
SQL Server chooses an index seek when predicates match the index key order and are selective. Otherwise, SQL chooses a scan to avoid expensive random lookups. Seek-friendly index design is critical for optimal performance.
Quick Answer
SQL Server uses Index Seek when the filter is selective (returns a small fraction of rows) and the column is part of an index with a matching leading key. It uses Index Scan when the filter is broad or the index doesn't match well. SARGable predicates are what make seeks possible.
S
SugharaIQ Editorial Team
Verified Answer
This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.
Want to bookmark, take notes, or join discussions?
Sign in to access all features and personalize your learning experience.
Source: SugharaIQ