December 19, 2012 at 2:45 am
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:
"WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances
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
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2012 at 6:13 am
Adi Cohn-120898 (12/19/2012)
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:
"WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances
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
I'm sorry, but I think you are going to extremes here in your argument discussion with GSquared.
Just because you CAN add a persisted computed column and index it force a seek when you have this where clause: "WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)", doesn't make it SARGable. Doing that, creating the index, just adds more overhead to system and does nothing to help someone learn better ways of writing code. You could say it treats the symtom, not the problem.
It the case of this WHERE clause, a simple rewrite is much easioer than changing the structure of the database to meet the query.
December 19, 2012 at 6:55 am
I'm sorry, but I think you are going to extremes here in your argument discussion with GSquared.
Just because you CAN add a persisted computed column and index it force a seek when you have this where clause: "WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)", doesn't make it SARGable. Doing that, creating the index, just adds more overhead to system and does nothing to help someone learn better ways of writing code. You could say it treats the symtom, not the problem.
It the case of this WHERE clause, a simple rewrite is much easioer than changing the structure of the database to meet the query.
I think that you misunderstood my message. Below is a quote from my message that says the exact thing that you claim- this is not a SARGable criteria:
Take for example your quote:
"WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances
I completely agree that this type of criteria is not SARGable
Maybe I wasn't clear in my writing, or maybe you took it out of context. It is clear to me and everyone that reads this thread that a query with this type of criteria can be improved by modifying the code and not by jumping hoops and loops as I did. GSquared gave his definition of SARGability and asked me for my definition. I explained that this discussion made me realize that I'm using a wrong definition (my definition was that the query is SARGable if the server can use seek operation without modifying the code) and showed this example as an explanation to show why my definition is wrong (e.g. According to the definition that I've used until this discussion a query like that should have been considered SARGable, but in reality it isn’t).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2012 at 6:56 am
Well, maybe we need a term like "circumstantial SARGability", to indicate that edge cases can be found where "normal SARGability" fails but a clever DBA can get seeks to happen anyway.
Would cover the situation you posed, without necessarily reducing the usefulness of the term in training general query writing skills.
The most important aspect of SARGability as a term is being able to use it in training people. That means keeping it simple, and letting them know that, "For most applications you will build using T-SQL, it's important to keep function-use out of Where clauses and Join math, because of ..." and go from there. As with everything databases, there are caveats, loopholes, "it depends", and "most of the time"s.
The main reason I want to keep the general term closely tied to writing queries, instead of related to specific object properties or data properties, is because that keeps it most useful in teaching query-writing.
Keeping it that way also helps with teaching database architecture. "In order to best support the SARGable queries your devs will be writing, you should design tables with the following in mind ..." That kind of thing.
More advanced querying techniques and architectural techniques will usual be done by people who understand the rules well enough to know when to violate them. At least, that's the hope. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 7:07 am
GSquared
SELECT *
FROM dbo.SARGTest
WHERE CAST(DT AS DATE) = CAST(GETDATE() AS DATE);
It is my belief that this query gets a seek (i.e. is SARGable) as a function-around-a-column because Microsoft KNEW it would be happening SO MUCH that they baked it into the optimizer/engine.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2012 at 7:14 am
TheSQLGuru (12/19/2012)
GSquared
SELECT *
FROM dbo.SARGTest
WHERE CAST(DT AS DATE) = CAST(GETDATE() AS DATE);
It is my belief that this query gets a seek (i.e. is SARGable) as a function-around-a-column because Microsoft KNEW it would be happening SO MUCH that they baked it into the optimizer/engine.
Entirely possible. But that same statement can be said about "WHERE Col1 = 5". Microsoft KNEW it would be happening SO MUCH that they baked it into the optimizer/engine.
That's true of everything SQL Server does, actually.
But I get what you're saying.
It's SARGable because of how SQL Server stores date-time data. The leading edge of a DateTime column in an index is the integer (date) portion of the numerical representation of the date. Very easy to seek that, regardless of other factors in the query/structure. Doesn't require a computed column or indexed view or anything special. Very simply put, the query can seek on the column if the column is the leading edge of an index, which is the basic concept of SARGability.
But, because it is a bit of an exception, maybe even an edge case (though date vs datetime is a frequent enough need that I'm not sure that applies), so maybe it needs to come under the heading I proposed of "circumstantial SARGability". Maybe a better term would be "extended SARGability". Not sure which of those I like better. Any opinions?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 7:20 am
TheSQLGuru (12/19/2012)
GSquared
SELECT *
FROM dbo.SARGTest
WHERE CAST(DT AS DATE) = CAST(GETDATE() AS DATE);
It is my belief that this query gets a seek (i.e. is SARGable) as a function-around-a-column because Microsoft KNEW it would be happening SO MUCH that they baked it into the optimizer/engine.
I have to agree with GSquared on this. I think it works (SARGable, uses an index) if DT is a date/time data type. If, for some reason (and we know it happens), the data type of DT is VARCHAR with the date/time stored in a valid character format I don't think you would get a SEEK due to the implicit conversion from character to date.
December 19, 2012 at 7:29 am
Have a look at the seek predicate for the DATE cast.
You will see that it has converted the predicate to a date range
ie
>= Date and < Date
So IMO the cast itself is not sargable, but the optimizer has converted it to logic that is.
December 20, 2012 at 7:17 am
Dave Ballantyne (12/19/2012)
Have a look at the seek predicate for the DATE cast.You will see that it has converted the predicate to a date range
ie
>= Date and < Date
So IMO the cast itself is not sargable, but the optimizer has converted it to logic that is.
Yep. Which fits with what I said about "extended SARGability". Not really SARGable in some sort of pure sense, but the optimizer is smart enough to deal with it anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply