Home Dashboard Directory Help
Search

Make more functions SARGable by Rob Farley


Status: 

Closed
 as Won't Fix Help for as Won't Fix


112
1
Sign in
to vote
Type: Suggestion
ID: 526431
Opened: 1/21/2010 9:47:37 PM
Access Restriction: Public
0
Workaround(s)
view

Description

WHERE cast(SomeDateTime as date) = SomeDate

...is SARGable. The system knows the order doesn't change, and it leverages this in the Query Optimizer.

An index on hierarchyid can take advantage of GetAncestor and other functions.

But as soon as we do:

WHERE datediff(day,0,SomeDateTime) = SomeNumber

...there is no Seek Predicate that can handle this. And yet the order of the rows doesn't change.

WHERE SomeString LIKE 'A%' --is fine, and translated into SomeString >= 'A' AND SomeString < 'B'
WHERE LEFT(SomeString,1) = 'A' --is not.
WHERE SomeNumber + 1 = 3 --is not.
WHERE SomeNumber / 10 = 3 --is not.
WHERE convert(char(6),SomeDate,112) = '200912' --is not.
WHERE convert(char(6),SomeDate,112) + '01' = '20091201' --is not.

...and yet none of these change the order of rows in the index.

These situations (and many more) should be turned into searchable arguments.
Details
Sign in to post a comment.
Posted by Microsoft on 2/1/2012 at 12:59 PM
Hello,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,

Susan Price
Senior Program Manager
SQL Server Database Engine
Posted by Itzik Ben-Gan on 12/2/2011 at 12:51 PM
Here's another repro that seems to be related:

set nocount on;
use tempdb;
go
drop function dbo.GetNums;
drop table tmporders, ordersidentity, orderssequence;
drop sequence dbo.seq1;
go

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
SELECT @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;
GO

create sequence dbo.seq1 minvalue 1;
go

create table dbo.tmporders
(
orderid int primary key,
name varchar(20) not null,
qty int not null
);

create table dbo.ordersidentity
(
orderid int primary key identity,
name varchar(20) not null,
qty int not null
);

create table dbo.orderssequence
(
orderid int primary key,
name varchar(20) not null,
qty int not null
);
go

insert into dbo.tmporders with (tablock) (orderid, name, qty)
select n, 'aaaaaaaaaaaaaaaaaaaa', n
from dbo.getnums(1, 10000);

-- no sort
insert dbo.ordersidentity (name, qty)
select name, qty
from dbo.tmporders;

-- sort
insert dbo.orderssequence (orderid, name, qty)
select next value for dbo.seq1 as orderid, name, qty
from dbo.tmporders;

Posted by deDeveloper on 5/26/2011 at 1:58 PM
I can't imagine how many poorly written procedures (not by me of course) would start running amazingly fast (maybe) if Microsoft could implement the query optimizer to determine if a function changed the order of rows. WOW!! Sometimes it is not even poorly written code, it is just the way it has to be done and SQL Server should be able to use the index.

Please inprove the optimizer to take advantage of an index when row order is not changed. Don't just decide because the clause is wrapped in a function it can't be done.
Posted by Microsoft on 1/25/2010 at 1:30 PM
Rob, Thanks for reporting it. I have made this as a 'design change request' and it is something that we can consider for the next release. This will need to be prioritized along with other competing requests from customers.

thanks
Sunil
Posted by Rob Farley on 1/24/2010 at 5:25 PM
I wrote a blog post on this - should've provided the link already, but don't seem to have - where I make that exact point of "I upgraded and things are just faster", particularly around poorly written third party applications, over which I don't have enough control to be able to change the queries. The post is at: http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx
Posted by TiborK on 1/24/2010 at 4:51 AM
I agree that a general overhaul of typical search expressions we see "out there", turning them into sargable conditions could be very beneficial. One of those "I upgraded and my app is now much quicker" things.
Sign in to post a workaround.