What is the fastest way to group a DateTime column by Date in T-SQL

I have an older sql 2005 box, and I need to do some summaries of a table with ~500m rows.

I have a date开发者_如何学JAVAtime column in the table and I want to get just the date out of it for output and group by. I know there are a few ways to do this, but what is the absolute fastest?

Thanks

I suspect the fastest would be to:

SELECT
  the_day = DATEADD(DAY, the_day, '19000101'), 
  the_count
FROM
(
  SELECT 
    the_day = DATEDIFF(DAY, '19000101', [the_datetime_column]), 
    the_count = COUNT(*)
  FROM dbo.the_table
  GROUP BY DATEDIFF(DAY, '19000101', [the_datetime_column])
  WHERE ...
) AS x;

But "fastest" is relative here, and it will depend largely on the indexes on the table, how you're filtering out rows, etc. You will want to test this against other typical date truncation methods, such as CONVERT(CHAR(8), [the_datetime_column], 112).

What you could consider - depending on whether this query is more important than write performance - is adding a persisted computed column with an index, or an indexed view, that would help this aggregation for you at write time instead of query time.

I imagine you can get a slightly better performance this way.

SELECT cast(cast([actiontime]+.5 as int) as datetime) as [yourdate], count(*) as count
FROM <yourtable>
GROUP BY cast([<yourdate>]+.5 as int)

You can improve this once you upgrade to mssql server 2008.

SELECT cast([<yourdate>] as date) as [yourdate], count(*) as count
FROM <yourtable>
GROUP BY cast([<yourdate>] as date)