Problem

What is the best way to find the number of rows in a SQL Server table?  What are various options in SQL Server to get a count of rows?  Can you provide any examples?

Solution

There are several ways to get the count of records for a SQL Server table and we will look at a few options in this tip.

Basic Usage of SQL Server COUNT Function

COUNT is an aggregate function in SQL Server which returns the number of items in a group. COUNT will always return an INT. COUNT will use indexes, but depending on the query can perform better with non-clustered indexes than with clustered indexes.

It is easiest to look at it through examples, so I will start with a rather contrived sample table like:

create table dbo.employees
 Id int identity(1, 1) primary key,
 FName varchar(25),
 LName varchar(25),
 Title varchar(25),
 PrimaryPhone varchar(12),
 OfficeLoc varchar(2)
insert into dbo.employees (FName, LName, Title, PrimaryPhone, OfficeLoc)
values
  ('Albert', 'Einstein', 'Physicist', '299-792-4580', 'OK'),
  ('Archimedes', 'Syracuse', 'Mathematician', '314-159-2653', 'NV'),
  ('Pythagoras', 'Samos', 'Mathematician', '141-421-3562', 'NV'),
  ('Roger', 'Penrose', 'Mathematician', '161-803-3988', 'NV'),
  ('Amedo', 'Avogadro', 'Chemist', '602-214-0857', 'NV'),
  ('Michael', 'Faraday', 'Physicist', '965-853-3289', 'NV'),
  ('Isaac', 'Newton', 'Physicist', '667-408-3100', 'NV'),
  ('Neil', 'Tyson', 'Astrophysicist', '195-810-0500', 'NY'),
  ('Emmy', 'Noether', 'Mathematician', '188-203-2300', 'NV');

-- this will return a value of 9  
select count(*) from dbo.employees  
Using COUNT in its simplest form, like: select count(*) from dbo.employees simply
returns the number of rows, which is 9.
SQL Server COUNT Function with Group By
COUNT is more interestingly used along with GROUP BY to get the counts of specific
information. If we wanted to know the number of each job title or position, we could
select Title, count(*) as PositionCount
from dbo.employees
group by title
Naturally, you can have several columns specified in the GROUP BY clause. So,
if we wanted to know the number of each job type in Nevada (NV) specifically, we could
put both Title and OfficeLoc in the GROUP BY clause and use a WHERE clause to limit
it to Nevada:
select Title, OfficeLoc, count(*)  as PositionCount
from dbo.employees
where OfficeLoc = 'NV'
group by Title, OfficeLoc
SQL Server COUNT Function with Group By and Having
If we want to limit the result set based on COUNT, or any other aggregate function,
we can do that through the HAVING clause. So, if we want a list of job titles that
are shared by three or more people:
select Title, count(*)  as PositionCount
from dbo.employees
group by Title
having count(*) >= 3
SQL Server COUNT Function with Distinct
We can use COUNT with the DISTINCT argument to have it only return the number
of different values in the selected row. It cannot be used with * and must have
an expression (such as a column or list of columns) specified. So, if we wanted
to know the number of different job titles used at each location, we could use:
select OfficeLoc, count(distinct Title) as NumJobTypes
from dbo.employees
group by OfficeLoc
We could also use an
OVER clause to partition the results. For instance, to get the number of employees
per location with an over clause, instead of a group by clause, we could use:
select distinct OfficeLoc, count(LName) over (partition by OfficeLoc) as NumEmployees
from dbo.employees
COUNT_BIG is an almost identical function that will always return a bigint value.
Its usage is essentially the same as COUNT other than being able to deal with larger
results.

@@rowcount is also in some ways related and returns the number of rows affected
by the last statement. So executing the below command will give a result of 9 for the @@rowcount.

select LName
from dbo.employees

select @@ROWCOUNT
Next Steps
The MSDN page covering COUNT is available
The MSDN page covering COUNT_BIG is available
SQL Server T-SQL Aggregate Functions is a closely related tip 
I enjoyed reading this post.
I found you have not dealt with COUNT Function and NULL values. It is an important as pect in count.
Say you are counting from a column if there are Nulls then COUNT function wount count.
If I am wrong please feel free to let me know.
Thanks for educating the community and appreciate your volunteership.
Thanks a bunch
Happy Holidays!