Count rows against to SQL server (2005) table?

Question

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?

Answer 1

You should add an index on the column dt, then it should be very fast.

CREATE INDEX ix_Table1_dt ON Table1 (dt)

Creating the index may take a long time if you have many rows (and I assume you do).

Answer 2

As Mark suggests, add an index and then use the COUNT(*). You're transferring a HUGE amount of data in option 1 just to count it and then throw it away.

Answer 3

Put indexes on your dt field and use COUNT(id) (this assumes id is set as NOT NULL)

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

Also, COUNT(id) vs COUNT(*) shouldn't really matter in your case.

Answer 4

What happens when you do this

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

just remember that COUNT(id) doesn't count NULLS while COUNT(*) does

do you have an index on the columns in your WHERE clause?