>SQL Server is cost-based. That means Query Processor (QP) evaluates a number of scenarios to determine how it should attack the problem and how it should return the rows.
>So even an insert/delete/update of 1 row can make a huge change between a scan or a seek in your execution plan.
>
>SQL Server prefers scan as it per definition is sequential. Seeks are random in nature. The underlying disks (SSD or SCSI) doesn't matter. Yet.
>
>You have probably encountered a tipping point where QP calcuates that a SEEK together with a KEY LOOKUP is less costly than a SCAN to get all columns in one sweep.
Thanks