Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 21, 2003

Working with SQL Server Date/Time Variables: Part Three - Searching for Particular Date Values and Ranges - Page 3

By Gregory A. Larsen

Finding First Record of the Month

Sometimes you may want a specific record, although do not know the exact date you need to search for to find it. You may want to find the RECORD number for the first record that was inserted in a given month. Since you don't know what the SAMPLE_DATE date and time might be for the first records, you will need to search for all records in the desired month, and use the TOP clause to return the first one. Here is an example that uses the LIKE Clause to return the first record that has a SAMPLE_DATE in April 2003.

SELECT TOP 1 RECORD FROM DATE_SAMPLE WHERE SAMPLE_DATE 
LIKE 'APR%2003%' ORDER BY SAMPLE_DATE

Note that I have used the ORDER BY clause. The reason for this is due to fact that records in SQL Server are not necessarily stored in order.

Performance Considerations

If you are searching large tables with lots of records, you will most likely index some of the date columns that are commonly used to constrain queries. When a date column is used in a where clause, the query optimizer will not use an index if the date column is wrapped in a function. In addition, using the LIKE clause to search for particular records will keep the query optimizer from using an index thus slowing down how long it takes SQL Server to complete your query. Let me demonstrate.

I have now placed a non-clustered index on column SAMPLE_DATE in the DATE_SAMPLE table called 'SD_IX'. Below there are two different SELECT statements I will be using for my example.

SELECT * FROM DATE_SAMPLE WHERE
SAMPLE_DATE >= '2003-04-09' AND SAMPLE_DATE <'2003-04-10'

SELECT * FROM DATE_SAMPLE WHERE
CONVERT(CHAR(10),SAMPLE_DATE,121) = '2003-04-09'

The first SELECT statement selects records without using a function, while the second select statement uses a CONVERT function. Both SELECT statements return the same results, all the records for '2003-04-09'. By issuing the "SET SHOWPLAN_TEXT ON", we can display the execution plans of each SELECT statement in TEXT format. If you review the execution plans (see below), you can see that the first SELECT statement uses an index seek on index 'SD_IX', while the second one only uses a table scan.

Execution Plan for first SELECt statement

 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), 
OBJECT:([master].[dbo].[DATE_SAMPLE]))
       |--Index Seek(OBJECT:([master].[dbo].[DATE_SAMPLE].[SD_IX]), 
SEEK:([DATE_SAMPLE].[SAMPLE_DATE] >= Convert([@1]) AND 
[DATE_SAMPLE].[SAMPLE_DATE] < Convert([@2])) ORDERED FORWARD)

Execution plan for the second SELECT statement.

  |--Table Scan(OBJECT:([master].[dbo].[DATE_SAMPLE]),
WHERE:(Convert([DATE_SAMPLE].[SAMPLE_DATE])='2003-04-09'))

Therefore, if performance is a consideration then it is best to write your code to make sure it can take advantages of available indexes. Of course if the table you are searching is quite small in the number of records it contains, then possibly the performance gains may not out weigh the simplicity of writing code that uses a function of some kind.

Conclusion:

There are always many different methods that can be used to search for records that contain dates and times, and different performance considerations with each. I hope that this article has given you some insight on the different ways to search SQL Server tables, using a date in the selection criteria.

My next article, regarding working with SQL Server date and time variables, will be the last in this series. It will discuss the use of the DATEDIFF, DATEADD, GETDATE and GETUTCDATE functions, and how these might be used in your applications.

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM