Careful when you use count() function

This was a silly mistake that I committed when writing a procedure.It took me great deal of time to figure out the reason. I thought I should share this.

create table #example (id_no int) --create a temporary table
insert into #example select 1
insert into #example select 2
insert into #example select 2
insert into #example select 4
insert into #example select null

when I execute a query like
select COUNT(id_no) from #example --output is 4

but when i execute the query like
select COUNT(*) from #example --output is 5

The reason I found was that if any row has a null value and if that columns is passed as parameter for count function then null values for that specific columns will be skipped. Only not null values will be taken into consideration.

using below query will result in taking only distinct not null values into consideration.
select COUNT(distinct id_no) from #example --output is 3

drop table #example

I would be glad to add more information on this topic if you give your valuable comments.

Basic SQL

I started learning basic SQL by creating tables and playing with it using different types of queries. For every question i came across, i wrote a query after analyzing the question. This helped me to learn simple stuff faster.

You can learn some quick SQL by reading Chapter 8: SQL - The Relational Database Standard database systems by Elmasri & Navathe.

A week after I joined my company, I was asked to write a SQL test and going though these basic queries helped me a lot.

A perfect start

Just after my post graduation, I got a wonderful opportunity to start my career in data warehousing. Every day I learn new concept and I thought it would be useful if I share my knowledge to the people who are pursuing their career in data warehousing or planning to start their career in data warehouse.

Currently I am involved in ETL (Extarct Transform Load) Process where my role is to extract data from the CSV (comma separated values) file and transform the extracted data using pre written procedures and then load it into database.

After working of the ETL for some time, I understood that my knowledge in using SQL was shallow. So I started to learn SQL from basics, yes you got it right, I started from basic select queries.

Currently I am working on SQL Server 2005, So my upcoming blogs will contain SQL queries using SQL Server.