what is the fastest way of getting table record count with condition on SQL Server

Question

As per subject, i am looking for a fast way to count records in a table without table scan with where condition

Answer 1

Simply, ensure that your table is correctly indexed for the where condition.

If you're concerned over this sort of performance the approach is to create indexes which incorporate the field in question, for example if your table contains a primary key of foo, then fields bar, parrot and shrubbery and you know that you're going to need to pull back records regularly using a condition based on shrubbery that just needs data from this field you should set up a compound index of [shrubbery, foo]. This way the rdbms only has to query the index and not the table. Indexes, being tree structures, are far faster to query against than the table itself.

How much actual activity the rdbms needs depends on the rdbms itself and precisely what information it puts into the index. For example, a select count()* on an unindexed table not using a where condition will on most rdbms's return instantly as the record count is held at the table level and a table scan is not required. Analogous considerations may hold for index access.

Be aware that indexes do carry a maintenance overhead in that if you update a field the rdbms has to update all indexes containing that field too. This may or may not be a critical consideration, but it's not uncommon to see tables where most activity is read and insert/update/delete activity is of lesser importance which are heavily indexed on various combinations of table fields such that most queries will just use the indexes and not touch the actual table data itself.

ADDED: If you are using indexed access on a table that does have significant IUD activity then just make sure you are scheduling regular maintenance. Tree structures, i.e. indexes, are most efficient when balanced and with significant UID activity periodic maintenance is needed to keep them this way.

Answer 2

There are different methods, the most reliable one is

Select count(*) from table_name

But other than that you can also use one of the followings

select sum(1) from table_name

select count(1) from table_name

select rows from sysindexes where object_name(id)='table_name' and indid<2

exec sp_spaceused 'table_name'

DBCC CHECKTABLE('table_name')

The last 2 need sysindexes to be updated, run the following to achieve this, if you don't update them is highly likely it'll give you wrong results, but for an approximation they might actually work.

DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.

EDIT: sorry i did not read the part about counting by a certain clause. I agree with Cruachan, the solution for your problem are proper indexes.