where cols like 'NA%'

The typical thing that will make a sql query non-sargable is to include a function in left part of a condition of a Where clause.

Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20

 Select ... WHERE 20 < DateDiff(mm,Date,GetDate()) 

The typical thing that will make a sql query non-sargable is to include a function in left part of a condition of a Where clause.

Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20

 Select ... WHERE 20 < DateDiff(mm,Date,GetDate()) 

--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
    DROP TABLE #testEnvironment;
END;
  
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID, 
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime,
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 30000 /*(Number of days in range)*/) + 1),CAST('1945' AS DATE) /*(Start date, e.g. '1945-01-01*/) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

CREATE CLUSTERED INDEX cl_testEnvironment_ID ON #testEnvironment(ID ASC);
CREATE NONCLUSTERED INDEX nc_testEnvironment_randomDate ON #testEnvironment(randomDate ASC);

DECLARE @TODAY DATE = GETDATE();

-- NON-SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE DateDiff(mm,randomDate,@TODAY) >= 20;

-- NON-SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE 20 <= DateDiff(mm,randomDate,@TODAY);

-- SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE randomDate < DATEADD(mm,-20,DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY)+1, 0));

where cols like 'NA%'

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'

DECLARE @tbl TABLE 
(
	ID INT IDENTITY(1,1),
	SomeData VARCHAR(100)
)

INSERT INTO @tbl SELECT 'NA11345'
INSERT INTO @tbl SELECT 'NA113456'
INSERT INTO @tbl SELECT 'RA11345'

UPDATE @tbl SET SomeData = SUBSTRING(SomeData,CHARINDEX('NA',SomeData)+2,LEN(SomeData))
WHERE CHARINDEX('NA',SomeData) = 1
select * from @tbl