I have to admit that it is very hard for me to define the term SARGable. Before we started our discussion in my opinion SARGable criteria was one that has the potential of using seek operation without modifying the query's code. Of course by that definition you are correct when you say that my example had nothing to do with SARGability. As you showed in your example when we have a clustered index the server does a seek operation without modifying the query's code. I also wrote that the server would still do a seek operation with none clustered index that is a covering index and based on the columns that were compared to the variables, which again showed that seek operation can be done without modifying the query's code.

The problem is that if we think only about the potential it will be very hard to find criteria that is not SARGable. We have so many tools today that can be used to turn scan operation into seek operation. We can create many columns with lots of included columns. We can create an indexed view on a table. We can use computed columns and index those columns. In reality many of the criteria that we both agree that is not SARGable can use seek operation if this will be our main goal. Take for example your quote:

I completely agree that this type of criteria is not SARGable, but I know that I can have such a query and with some modifications to the table and indexes the server will do a seek operation. This of course contradicts the definition that I was using before our discussion and the definition that you wrote. The example bellow shows how the same condition that both of us agreed that is not SARGable, can use seek operation.:

CREATE TABLE dbo.SARGTest (

DT DATETIME NOT NULL, filler char(1) default ('a'));

GO

--Inserting the records

INSERT INTO dbo.SARGTest(DT)

SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())

FROM sys.objects

CROSS JOIN sys.objects as s2 CROSS JOIN sys.objects as s3

--Creating an index that won't be used

CREATE INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);

go

--As expected we got a table scan

SELECT * FROM dbo.SARGTest

WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), DT, 112)

go

--Adding a computed column to the table

alter table dbo.SARGTest add ConvertedDate as convert(varchar(25), DT, 112)

go

--Adding an index on the computed column

create index ix_SARGTest_DT_INCLUDE_filler on dbo.SARGTest(ConvertedDate) include (filler, DT)

go

--Getting an index seek

SELECT * FROM dbo.SARGTest

WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), DT, 112)

Maybe we should use the term SARGablity with connection of the database's current structure and not with the query's potential to use seek operation if I'll modify the database.

Adi