DB Performance and data types

Question

I'm supporting an existing application written by another developer and I have a question as to whether the choices the data type the developer chose to store dates is affecting the performance of certain queries.

Relevant information: The application makes heavy use of a "Business Date" field in one of our tables. The data type for this business date is nvarchar(10) rather than a datetime data type. The format of the dates is "MM/DD/YYYY", so Christmas 2007 is stored as "12/25/2007".

Long story short, we have some heavy duty queries that run once a week and are taking a very long time to execute.

I'm re-writing this application from the ground up, but since I'm looking at this, I want to know if there is a performance difference between using the datetime data type compared to storing dates as they are in the current database.

Answer 1

You will both save disk-space and increase performance if you use datetime instead of nvarchar(10).

If you use the date-fields to do date-calculation (DATEADD etc) you will see a massive increase in query-execution-speed, because the fields do not need to be converted to datetime at runtime.

Answer 2

Operations over DATETIMEs are faster than over VARCHARs converted to DATETIMEs.

If your dates appear anywhere but in SELECT clause (like, you add them, DATEDIFF them, search for them in WHERE clause etc), then you should keep them in internal format.