How to Efficiently Retrieve Last Month's Data in SQL Server?

How to Retrieve Last Month's Records in SQL Server

To obtain records from a specific time period, a common practice is to utilize range queries with inclusive lower and upper bounds. However, this approach poses two challenges: compromising index utilization and potentially selecting unintended data.

1. Index Utilization:

Applying functions to a searched column, including implicit functions like casting, can force the optimizer to disregard indices on that column, resulting in a linear search through every record. Consider timestamps, which are typically stored as a chronological count. Extracting date parts from such timestamps requires complex computations, slowing down the query.

2. Unintended Data:

Inclusive upper-bound ranges, such as <=, can lead to incorrect data selection, depending on the SQL Server version and column data types. Data from midnight of the next day or records from portions of the current day may be erroneously included or excluded.

Correct Approach:

To address these issues, use the following query structure:

WHERE date_created >= @startOfPreviousMonth AND date_created < @startOfCurrentMonth

To determine the start of the previous and current months, use the following calculations:

@startOfPreviousMonth = DATEADD(month, -1, @startOfCurrentMonth)
@startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

This approach ensures index utilization and accurate data selection. The full script would be:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

SELECT *
FROM Member
WHERE date_created >= DATEADD(month, -1, @startOfCurrentMonth)
AND date_created < @startOfCurrentMonth

By performing date operations only once on a single value, the optimizer can leverage indices, and the query returns correct data.
The above is the detailed content of How to Efficiently Retrieve Last Month's Data in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!