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) – return all Non-NULL values

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(*) retruns 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 duplicates from count(Name) use COUNT(distinct Name).

select COUNT(distinct Name) from #tempTable –returns 3.

Difference between Class and Structure in .NET

In this article we will see what are all the differnece between a class and structure.

Structure	Class
Value type	Reference type
After instantiation memory will be created on stack.	After instantiation memory will be created on heap
Does not Support inheritance. But can implement interfaces	Supports inheritance and implementation of interfaces
Does not support default( parameter less )constructor	It supports default(parameter less) constructor
Cannot define destructor	Can have destructor
Does not permit initialization of instance fields with in definition	Permits initialization of instance fields with in definition
On assignment values data is copied	On assignment copies the reference
Memory deallocation done when they go out of scope	Memory deallocation handled by garbage collector.

Difference between Primary and unique key in SQL Server

Primary key vs. unique key

In this article we will see the difference between primary key and unique key with practical scenarios.

 

Primary Key	Unique Key
A table can have only one primary key	A table can have any number of unique keys
Does not allow null value so implicitly enforces not NULL constraint	Allows only one NULL value
Creates clustered index on the column	Creates non-clustered index on the columns

In the below script we will see the above points.

--table Create table PrimaryVsUniqueKey( id int constraint PK primary key, Col1 varchar(50) CONSTRAINT UniqueKey1 unique,Col2 varchar(50) constraint UniqueKey2 unique) --insert statements Insert into PrimaryVsUniqueKey values(1,'Ram','Laxman') --success Insert into PrimaryVsUniqueKey values(1,'Ajay','Vijay') --error as Violation of PRIMARY KEY constraint 'PK'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey' The statement has been terminated. Insert into PrimaryVsUniqueKey values(2,'Ram','Vijay') --error as Violation of UNIQUE KEY constraint 'UniqueKey1'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey'.The statement has been terminated. Insert into PrimaryVsUniqueKey values(Null,'John','Cena') -- error as Cannot insert the value NULL into column 'id', table 'Practise.dbo.PrimaryVsUniqueKey'; column does not allow nulls. INSERT fails. The statement has been terminated. Insert into PrimaryVsUniqueKey values(2,Null,Null) -- success Insert into PrimaryVsUniqueKey values(3,Null,'Test') -- Error as Violation of UNIQUE KEY constraint 'UniqueKey1'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey'. select * from PrimaryVsUniqueKey

Output: