|
Difference between count(*) and count(column_name)
|
|
|
We might assume that count(*) and count(column_name) will return same result count. But NO, in case of column holds any null values.
Count (*) – returns all values (including nulls and duplicates)
Count (Column_Name) – returns all Non-NULL values(including duplicates)
In the below script we will see how it works. So that it will be easy for us to understand.
create table #tempTable(Name char(1))
insert into #tempTable values("A")
insert into #tempTable values("B")
insert into #tempTable values(Null)
insert into #tempTable values("C")
insert into #tempTable values(Null)
insert into #tempTable values("C")
select COUNT(*) from #tempTable
select COUNT(Name) from #tempTable
drop table #tempTable Output: 6 and 4
The table #temptable has total 6 rows. Count(*) returns all the rows including null/duplicates but where as count(name) returns only 4 rows which includes duplicates("C") but not null values.
If you want to remove the duplicates from count(Name) then use distinct keyword in it.
select COUNT(distinct Name) from #tempTable –-returns 3
|
|
|
|
|
Does UNIQUE KEY supports more than one NULL Value?
|
|
|
No. In SQL server we can insert only one NULL value to column which has UNIQUE KEY constraint.
Example:CREATE TABLE SamepleUnique
(
Id INT PRIMARY KEY,
Name varchar(20) UNIQUE
)
INSERT INTO SamepleUnique VALUES(1,'Ram')
INSERT INTO SamepleUnique VALUES(2,'Krish')
INSERT INTO SamepleUnique VALUES(3,Null)
INSERT INTO SamepleUnique VALUES(4,Null) --Error as Violation of UNIQUE KEY constraint 'UQ__SamepleU__737584F603317E3D'. Cannot insert duplicate key in object 'dbo.SamepleUnique'.
--The statement has been terminated.
|
|
|
|