Make more functions SARGable by Rob Farley

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

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;

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.

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

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

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.