I have a simple question with two options to get count of rows in a SQL server (2005). I am using VS 2005. There are two options to get the count:

SELECT id FROM Table1 WHERE dt >= startDt AND dt < endDt;;

I get a list of ids from above call in cache and then I get count by List.Count. Another option is

SELECT COUNT(*) FROM Table1 WHERE dt >= startDt AND dt < endDt;

The above call will get the count directly. The issue is that I had several cases of exceptions with the second method: timeout. What I found is that the table1 is too big with millions of data. When I used the first option, it seems OK.

I am confused by the fact that Count() takes more time than getting all the rows(is that true?). Not sure if the aggregation call with Count() would cause SQL server to create temporary table or cache on server side and it would result in slow performance when table is too big? I am not sure what is the best way to get the count?