Why these two queries get similar time (around 52 seconds for over 2 milions rows)
sql, sql-server, sql-server-2008
Solution
For the second query, you can speed it up by adding an index on the date column.
For the first query, you need to make two changes. First create an index on the date column, and then change the query to use a between instead of a function on the left side of the equals. Search for the date between January 1 12:00am and December 31 11:59:59 pm of the target year. That way SQL Server can use the index.
Problem
I'm using the SQL Server 2008. I need your advice for why these two queries get similar time (around 52 seconds for over 2 millions rows): Query 1: ``` DBCC DROPCLEANBUFFERS DECLARE @curr INT SET @curr = YEAR(GETDATE()) SELECT MAX([Date]) FROM DB_Item WHERE YEAR([Date]) = @curr ``` Query 2: ``` DBCC DROPCLEANBUFFERS SELECT MAX([Date]) FROM DB_Item ``` With using Actual Execution Plan, I see it scans with `Clustered Index scan`. So, why is it and do we have another way to get `Date`'s maximum in 1 table quickly? Your help is highly appreciated. Thanks.