Z86APuEs

1SQL SERVER can to utilize an index seek operation, if an index exists
2"Pro SQL Server Internals"
3	
4WHERE name like 'SARGable%'
5WHERE name like '%non-SARGable%'
6	
7DROP TABLE dbo.Testing;
8GO
9
10CREATE TABLE Testing (
11    WeirdDatatype   int NOT NULL,
12    SomethingElse   char(200)
13);
14
15CREATE NONCLUSTERED INDEX IDX_ALWAYS_SARGable
16    ON dbo.Testing( SomethingElse);
17
18CREATE NONCLUSTERED INDEX IDX_NOT_ALWAYS_SARGable
19    ON dbo.Testing(SomethingElse);
20
21INSERT INTO dbo.Testing
22        ( WeirdDatatype, SomethingElse )
23SELECT TOP 1000 m.message_id, CONVERT(char(200), m.text)
24FROM sys.messages AS m;
25	
26SELECT *
27FROM dbo.Testing AS t
28WHERE  t.WeirdDatatype = 1001;
29SELECT *
30FROM dbo.Testing AS t
31WHERE t.SomethingElse LIKE 'Line%'
32SELECT *
33FROM dbo.Testing AS t
34WHERE t.SomethingElse LIKE '%Line%'
35     AND t.WeirdDatatype = 1001;