While it's quite cool that there are CASTs that still allow seeks, in this case you would still want to look into avoiding the CAST.

As discussed at http://dba.stackexchange.com/questions/34047/cast-to-date-is-sargable-but-is-it-a-good-idea, there are still things about that plan that are suboptimal.

For one thing, the estimate for the number of rows coming out of the seek can be quite a bit more inaccurate when you do the CAST and force the dynamic seek pattern.

The performance of the version with the CAST is also not quite as good, so I avoid it if at all possible.

To illustrate:

CREATE TABLE test_dates (some_datetime datetime);

WITH n1(x) AS (SELECT 1 UNION ALL SELECT 1), -- 2 rows

n2(x) AS (SELECT 1 FROM n1 a, n1 b), -- 4 rows

n3(x) AS (SELECT 1 FROM n2 a, n2 b), -- 16 rows

n4(x) AS (SELECT 1 FROM n3 a, n3 b), -- 256 rows

n5(x) AS (SELECT 1 FROM n4 a, n4 b), -- 65536 rows

tally(x) AS (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM n5)

INSERT INTO test_dates

SELECT DATEADD(minute,x,'20130101')

FROM tally;

CREATE CLUSTERED INDEX CI_some_datetime ON test_dates (some_datetime);

GO

CREATE PROCEDURE test_no_cast

AS

DECLARE @datetime_bucket as datetime

SELECT @datetime_bucket=some_datetime

FROM test_dates

WHERE some_datetime BETWEEN '20130101' AND '20130103';

GO

CREATE PROCEDURE test_sargable_cast

AS

DECLARE @datetime_bucket as datetime

SELECT @datetime_bucket=some_datetime

FROM test_dates

WHERE CAST(some_datetime AS DATE) BETWEEN '20130101' AND '20130102';

GO

EXEC test_no_cast

GO 1000

EXEC test_sargable_cast

GO 1000

SELECT proc_name=object_name(object_id, database_id),

total_logical_reads,

total_worker_time,

total_elapsed_time

FROM sys.dm_exec_procedure_stats ps

WHERE object_name(object_id, database_id) LIKE 'test%';

DROP PROCEDURE test_no_cast, test_sargable_cast;

DROP TABLE test_dates;

On my instance the actual number of rows returned is 2880 for the non-CAST version and 2879 for the SARGable CAST (the two are not logically the same because the non-CAST version will return midnight of the upper boundary).

The estimated rows for the non-CAST version is 2879.71, acceptably close 🙂

The estimated rows for the SARGable CAST is 1439.86, rather off, especially if that under-estimate gets propagated through the rest of a more complex plan.

Here are the performance results on my lab machine:

proc_name total_logical_reads total_worker_time total_elapsed_time

------------------------------ -------------------- -------------------- --------------------

test_sargable_cast 9000 994330 995330

test_no_cast 9000 491606 492603

While in absolute terms that's not a crushing difference for 1000 executions, it's a pretty significant percentage difference, and there's really no point conceding that performance just to use a CAST that happens to be able to use a seek, however nifty that might seem 🙂

Especially when combined with the potential cardinality estimation problems, I'd avoid utilizing these SARGable CASTs unless there's a really good reason to use them.

Cheers!