>SQL Server: Data Type DATETIME or Only DATE


SQL Server 2008 introduced a new data type “DATE” to store date data only. Before this we have only DATETIME data type which stores both date and time. I have observed that many database developers and DBAs still prefer DATETIME to store data, even for columns where they don’t need to store time. For example, to store date of birth we need only date, and not the time.
SELECT Emp_Name, Emp_SSN, Emp_DOB
FROM Employee
WHERE CONVERT(VARCHAR(20), Emp_DOB, 101) <= ’06/30/2005′
Problem occurs when we need to retrieve such data, and we need different conversion functions to separate date from time. And if such conversion functions are part of WHERE clause, then it prevents the optimizer from choosing the index on the column. Resultantly, a poor query performance, because indexes are not being used.
Simply select DATE data type for such columns, especially when you don’t need the time portion. Even, if most of queries use conversion functions to separate date or time. Save such data separately in two different columns with data type DATE and TIME respectively.

Posted on February 16, 2011, in Index, Performance Tuning, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: