I was so surprised by this that I decided to do a test.

CREATE TABLE #john (JID int IDENTITY(1,1), testcol char(36));

-- Insert over a million random rows

WITH A1(N) AS (SELECT 0 UNION SELECT 1)

, A2 AS (SELECT a.N FROM A1 a CROSS JOIN A1 b)

, A3 AS (SELECT a.N FROM A2 a CROSS JOIN A2 b)

, A4 AS (SELECT a.N FROM A3 a CROSS JOIN A3 b)

, A5 AS (SELECT a.N FROM A4 a CROSS JOIN A4 b)

, A6 AS (SELECT a.N FROM A5 a CROSS JOIN A3 b)

INSERT INTO #John

SELECT CAST(NEWID() AS char(36))

FROM A6

-- Create indexes

ALTER TABLE #john ADD CONSTRAINT PK_john_JID

PRIMARY KEY CLUSTERED (JID)

CREATE NONCLUSTERED INDEX IX_john_testcol

ON #john(testcol)

-- Sargable

SELECT testcol FROM #john WHERE testcol LIKE 'AB%'

-- Not sargable

SELECT testcol FROM #john WHERE LEFT(testcol,2) = 'AB'

And it's true - the first SELECT used an index seek, the second an index scan. I'd have thought that the query optimizer would have been smart enough to realise that an index could still be used for LEFT.

John