Phil Parkin (12/21/2012)
eklavu (12/20/2012)
UPDATE #TEST_TABLESET COLS = SUBSTRING(COLS,3,LEN(COLS)-2)
WHERE LEFT(COLS,2) = 'NA'
Possibly not relevant here, but if the 'COLS' column is indexed, a better option would be
where cols like 'NA%'
As this will make use of the index, whereas the LEFT function is non-SARGable and will not.
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