Count in SQL

Recently I came across a fix that needed me to optimize performance as Count(*) used a lot of memory. My fix was Count(Col_name) which yielded wrong results. So I decided to pen down this based on some google search:

Doing COUNT(*) on a table with a lot of columns and a lot of rows can take a lot of time and memory. There is a temptation to do COUNT([Name]) where [Name] is the name of a column in the table. This can produce some surprising results. This is because of the way that COUNT() works.
It counts the results of the query placed inside of it. SQL Server does a trick with the * in COUNT(*). It does count the rows. But COUNT([Name]) counts only the results that are not NULL. This can throw your counts off if it is not what you are expecting. So use COUNT(1) to be sure of what you are getting in spite of NULL.
See the below script and result:

Temporary tables, CTEs, TVPs and Views in SQL

Say I have the following tables:

Department Table

Dept ID	Dept Name
1	Java
2	DotNet

Employee Table

Emp ID	Employee Name	Dept ID	Gender
12	A	2	F
13	B	2	M
14	C	1	F

And I want a table that gives me the department in which total number of employees >=2 i.e. Dotnet :

Alternative 1: via Views

One way to accomplish this is via Views as

Create View Dep_Emp As (Select DeptName, Count(*) As TotalEmpCount from Employee join Department on Employee.DeptID= Department.DeptID group by DeptName)

 

Dep_Emp View would be created as follows:

DeptName	TotalEmpCount
Java	1
DotNet	2

Then when we run the query:

Select DeptName from Dep_Emp where TotalEmpCount >= 2

We would get the result DotNet, and the view would be created SQL Server under System Databases > tempdb >Views

If we are not going to use the view in multiple places we will have to drop the View as the view will be of no great use in the future.

Alternative 2: Insert into new Tables(Temporary)

I can run the following query with Select into, and then drop the table

Select DeptName, Count(*) As TotalEmpCount  into #tblEmp  from Employee join Department on  Employee.DeptID= Department.DeptID group by DeptName Select DeptName from #tblEmp where TotalEmpCount >= 2 Drop table #tblEmp

 

Select DeptName, Count(*) As TotalEmpCount

 into #tblEmp

 from Employee join Department on Employee.DeptID= Department.DeptID group by DeptName

Select DeptName from #tblEmp where TotalEmpCount >= 2

Drop table #tblEmp

Here #tblEmp is a temporary table and it exists as long as the current session of the user exists.

 The temporary table denoted with a single # symbol is stored in SQL Server under System Databases > tempdb >Temporary Tables and is deleted when the last connection to the db referencing the #tblEmp table is closed.

Alternative 3: Using Table Variable Parameters

We could create a TVP as shown below by declaring the type of table as:

	Declare @tblEmp table (DeptName nvarchar(20), empCount int(19,5)) Insert into @tblEmp Select DeptName, Count(*) As TotalEmpCount from  Employee join Department on Employee.DeptID= Department.DeptID group by DeptName Select DeptName from @tblEmp where TotalEmpCount >= 2

 

This would create a TVP under tempDb of SQL Server

The beauty of TVPs is that

i)                    We do not have to drop TVPs , their scope exists in the statement block, stored procedure or batch in which the TVP is declared

ii)                   We can pass these as parameters to functions

Alternative 4: Using Common Table Expressions/CTEs

Note:

i)                    The scope of the CTE exists as long as the statement following the CTE is executed (over here it is the statement “Select DeptName from Employee_Count where TotalEmpCount >= 2 “)

ii)                   This statement that follows the CTE can be an Insert, update or select statement.

iii)                 There can be multiple CTEs inside the With and all those CTEs together have the scope until the statement that follows the CTE creation ie duration of the query.

Sub queries vs Derived Tables in SQL

Sub queries are best defined as query within a query. An example of a sub query is as follows:

select employee_name

from employee

where employee_salary >

-- this is a subquery:

(select avg(employee_salary)

        from employee)

Here, the subquery is within the where clause. It could be put in the in clause as well as:

Select * from employee where employee in(Select employee from employee_details where salary > avg(salary)

Now consider the following set of queries:

Create view view1 as Select salary from employee (this is for the query in the from clause)

Select max(salary) from view1

Drop view1

This set of queries using view involves writing values to the system catalog tables and three statements have to be written. Instead we could use a derived table.

Derived table is a table which is temporary , and created when you write a query within the From Clause as in:

Select max(salary) from (select salary from employee) As Max_salary

The point to be noted here is that the use of As clause is a must , without which we would get the error : “Every derived TABLE must have its own alias”.

Benefit of Derived table over using views:

i)                    No entry is made to the system catalog table.

ii)                   The temporary table made for the query within the from clause exists until the query is executed i.e the duration of the query

iii)                 It is faster than queries with views.