Styjun

I have two tables, both of which containing millions of rows of data.

tbl_one:
purchasedtm DATETIME,
userid INT,
totalcost INT

tbl_two:
id BIGINT,
eventdtm DATETIME,
anothercol INT

The first table has a clustered index on the first two columns: CLUSTERED INDEX tbl_one_idx ON(purchasedtm, userid)

The second one has a primary key on its ID column, and also a non-clustered index on the eventdtm column.

I want to run a query which looks for rows in which purchasedtm and eventdtm are on the same day.

Originally, I wrote my query as:

WHERE CAST(tbl_one.purchasedtm AS DATE) = CAST(tbl_two.eventdtm AS DATE)

But this was not going to use either of the two indexes.

Later, I changed my query to this:

WHERE tbl_one.purchasedtm >= CAST(tbl_two.eventdtm AS DATE)
AND tbl_one.purchasedtm < DATEADD(DAY, 1, CAST(tbl_two.eventdtm AS DATE))

This way, because only one side of the comparison is wrapped in a function, the other side can still use its index. Correct?

I also have some additional questions:

• I can write the query the other way around too, i.e. keeping tbl_two.eventdtm untouched and wrapping tbl_one.purchasedtm in CAST(). Would that make a difference in performance?


• If the answer to the previous question is yes is it because eventdtm has its own dedicated index, while looking up purcahsedtm would only be a partial index match?


• Are there other factors I can take into consideration for deciding which of the two choices is better? (For example, if there are millions of rows in tbl_one but billions of rows in tbl_two, would that impact which column I should CAST and which one I should not?)


• In genera, if you compare two columns that are both indexed, would we gain any performance compared to a similar scenario in which only one of them is indexed?


• And lastly, can I perform my original task without using CAST?

Note: I do not have the ability to create or modify indexes, add columns, etc.

Little. late after commenting but...

As discussed in the comments, code such as CAST(DateTimeColumn AS date) is actually SARGable. Rob Farley posted an article on some of the SARGable and non-SARGable functionality here, however, I'll cover a few things off anyway.

Firstly, applying a function to a column will normally make your query non-SARGable, and especially if it changes the order of the values or the order of them is meaningless. Take something like:

SELECT *
FROM TABLE
WHERE RIGHT(COLUMN,5) = 'value';

The order of the values in the column are utterly unhelpful here, as we're focusing on the right hand characters. Unfortunately, as Rob also discusses:

SELECT *
FROM TABLE
WHERE LEFT(COLUMN,5) = 'value';

This is also non-SARGable. However what about the following?

SELECT *
FROM TABLE
WHERE Column LIKE 'value%';

This is, as the logic isn't applied to the column and the order doesn't change. If the value wehre '%value%' then that too would be non-SARGable.

When applying logic that adds (or subtracts) what you want to find, you always want to apply that to the literal value (or function, like GETDATE()`). For example one of these expressions is SARGable the other is not:

Column + 1 = @Variable --non-SARGable
Column = @Variable - 1 --SARGable

The same applies to things like DATEADD

@DateVariable BETWEEN DateColumn AND DATEADD(DAY, 30,DateColumn) --non-SARGable
DateColumn BETWEEN DATEADD(DAY, -30, @DateVariable) AND @DateVariable --SARGable

Changing the datatype (other than to a date) rarely will keep a query SARGable. CONVERT(date,varchardate,112) will not be SARGable, even though the order of the column is unchanged. Converting an decimal to an int, however, had the same result as converting a datetime to a date, and kept SARGability:

CREATE TABLE testtab (n decimal(2,1) PRIMARY KEY CLUSTERED);
INSERT INTO testtab
VALUES(0.1),
 (0.3),
 (1.1),
 (1.7),
 (2.4);
GO

SELECT n
FROM testtab
WHERE CONVERT(int,n) = 2;
GO 

DROP TABLE testtab;

Hopefully, that gives you enough to go on, but pelase do ask if you want me to add anything further.

It is possible to create a persisted computed column in both tables that contains the date part only:

purchasedt AS CAST(purchasedtm AS DATE)
eventdt AS CAST(eventdtm AS DATE)

And create an index on it.

Regarding your original query: SQL Server can translate this:

WHERE CAST(tbl_one.purchasedtm AS DATE) = CAST(tbl_two.eventdtm AS DATE)

Into something similar to:

WHERE tbl_one.purchasedtm BETWEEN -- first ms of tbl_two.eventdtm
 AND -- last ms of tbl_two.eventdtm

But in your case (i) it will be have to calculate this for millions of rows inside tbl_two (ii) it will have to perform range scan inside a loop. SQL Server might not use the index.

The indexed date column will result in an equality comparison and no conversion.