You execute the following code: CREATE TABLE dbo.Customers ( id int PRIMARY KEY, CustomerName char(10) ) You create a nonclustered index named IX_CustomerName on the CustomerName column. You execute t

Using LIKE 'a%' is a sargable predicate because SQL Server can perform an index seek on the leading characters of the CustomerName column using the existing nonclustered index IX_CustomerName. The LEFT() function wraps the column and forces a full scan, but LIKE with a trailing wildcard allows the optimizer to scan only the relevant range of index entries, directly reducing query execution time.

Partitioning the table on CustomerName adds management overhead and does not make the predicate sargable, so the index still cannot be used for an efficient seek.

SUBSTRING() is also a scalar function applied to the column, making the predicate non-sargable just like LEFT(), so the optimizer cannot use the index for a seek.

Replacing the nonclustered index with a clustered index changes physical storage order but does not make the LEFT() predicate sargable; the query would still cause a full index scan.