SQL Server Datetime Cast to Date type can use Index (reprint)

A long time did not write blog, not lazy, it is recently my visit speed is not good, with Firefox often can't upload pictures ....

I didn't mean to find out today. SQL Server Datetime Cast to Date type you can use the index to share:

Test environment:

Use TEMPDB
GO

CREATE TABLE TB
(
ID INT IDENTITY (PRIMARY KEY),
NAME VARCHAR (200),
OPTIME DATETIME DEFAULT GETDATE ()
)
GO
DECLARE @i INT = 1
While @i<10001
BEGIN
INSERT into TB (NAME) SELECT ' A ' +ltrim (@i)
SET @[email protected]+1
END
GO
INSERT into TB (name,optime) SELECT ' A10001 ', ' 2010-05-27 16:25:20.117 '
GO
CREATE INDEX ix_optime on TB (OPTIME)
GO

As can be seen from the above T-SQL, if we look at the May 2010 27 data, there should be only one.
To more clearly illustrate the differences between the following four formulations, open the io/execution Plan switch, and select the execution result that contains the actual execution plan

SET STATISTICS IO on
SET STATISTICS profile on

Here are four ways to do this:

The first type of notation:

SELECT * from TB WHERE CONVERT (VARCHAR (), optime,120) = ' 2010-05-27 '

Message Result:

(1 row (s) affected)
Table ' TB '. Scan count 1, logical read 40 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Execution Plan:

　　　　
With the above execution plan, you can see that a clustered index scan will scan all the index leaves, which is not what we want, and it cannot effectively use the index.

The second way:

SELECT * from TB WHERE LTRIM (Year (OPTIME)) + '-' +ltrim (MONTH (OPTIME) + '-' +ltrim "(Day (OPTIME)) = ' 2010-5-27 '

Message Result:

(1 row (s) affected)
Table ' TB '. Scan count 1, logical read 40 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Execution Plan:

Similarly, the second approach is as inefficient as the first.

The third type of notation:

SELECT * from TB WHERE OPTIME between ' 2010-05-27 00:00:00.000 ' and ' 2010-05-27 23:59:59.999 '

Message Result:
(1 row (s) affected)
Table ' TB '. Scan count 1, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Execution Plan:

　　　　
As can be seen from the above results and the execution plan, it is efficient to use the nonclustered index effectively. But it needs to be added to the scope of the day. that is ' 00:00:00.000 ' and ' 23:59:59.999 '.

The fourth type of notation:

SELECT * from TB WHERE CAST (OPTIME as DATE) = ' 2010-05-27 '

Message Result:
(1 row (s) affected)
Table ' TB '. Scan count 1, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Execution Plan:

from the above results we can see that, although the use of cast transformation data type, but still can effectively use the index, read the page number is 4, and the third way, the same efficient. 　　

By viewing the Execution Plan stmttext field discovery:
SELECT * FROM [TB] WHERE CONVERT ([date],[optime],0) [email protected]
|--nested Loops (Inner Join, OUTER REFERENCES: ([tempdb].[ DBO]. [TB]. [ID]))
|--nested Loops (Inner Join, OUTER REFERENCES: ([Expr1006], [Expr1007], [Expr1005]))
| |--compute Scalar (DEFINE: ([[expr1006],[expr1007],[expr1005]) =getrangethroughconvert (' 2010-05-27 ', ' 2010-05-27 ', ( 62)))
|    | |--constant Scan
| |--index Seek (OBJECT: ([tempdb].[ DBO]. [TB]. [Ix_optime]), SEEK: ([tempdb]. [dbo]. [TB]. [OPTIME] > [Expr1006] and [tempdb]. [dbo]. [TB]. [OPTIME] < [Expr1007]), Where: (CONVERT (date,[tempdb].[ DBO]. [TB]. [optime],0] = ' 2010-05-27 ') ORDERED FORWARD)
|--clustered Index Seek (OBJECT: ([tempdb].[ DBO]. [TB]. [PK__TB__3214EC27753864A1]), SEEK: ([tempdb]. [dbo]. [TB]. [Id]=[tempdb]. [dbo]. [TB]. [ID]) LOOKUP ORDERED FORWARD)

Execution plan will ' 2010-05-27 ' get Expr1006 and Expr1007, then go to index find:optime>Expr1006 and OPTIME <Expr1007.

So Expr1006 and Expr1007 are ' 2010-05-26 23:59:59.998 ' and ' 2010-05-28 00:00:00.000 '? I don't know, but I look like. What do you think?

　　　　

If there is any mistake, please correct me, thank you.

SQL Server Datetime Cast to Date type can use Index (reprint)