Difference between Where and Having

Below list is major difference between where and having in SQL Server;

Where	Having
The WHERE clause selects rows before grouping	HAVING clause selects rows after grouping.
Where clause states the criteria which individual records should  meet to be selected by a query , used without the GROUP BY clause.	HAVING clause cannot be used without the GROUP BY clause.
WHERE clause cannot contain aggregate functions	HAVING clause can contain aggregate functions.
WHERE is used before the aggregation takes place.	HAVING is used to check conditions after the aggregation takes place.
select City, CNT=Count(1) From Address Where State = 'MA' Group By City	select City, CNT=Count(1) From Address Where State = 'MA' Group By City Having Count(1)>5

Difference between Delete and Truncate

Below list is major difference between Delete and Truncate in SQL Server;

	Delete	Truncate
Command Type	It is DML command Type.	It is DDL command Type.
Filter	Specify the filter for command like where.	Can’t use Where clause.
Reset Identity	Delete command won’t reset the identity Column.	Truncate command  reset the identity Column.
Performance	Slower than truncate as it keeps logs	High performance  as it won’t keep log.
RollBack	Rollback is possible.	Rollback is not possible.
Activate Trigger	Delete activates a trigger.	TRUNCATE cannot activate a trigger
Locked Behaviour	DELETE statement lock row, each row in the table is locked for deletion.	TRUNCATE always locks the table and page but not each row.

Difference between Datetime and Datetime2

Below list is major difference between Datetime and Datetime2 in Sql Server;

	Datetime	Datetime2
Storage Size	8 byte	6-8 Byte.
Min Value	1753-01-01 00:00:00	0001-01-01 00:00:00
Current Datetime	GetDate() - returns DB Current DateTime of datetime Data Type	SYSDATETIME()  - returns Current DateTime of DateTime2 Data Type
+/- Operator	+/- operator works, e.g  GetDate() + 1	+/- operator not works; DateAdd function required to add date.
Syntax	Declare @now datetime	Declare @now datetime2(7)

Difference between Store Procedure and User defined Function

Below list is major difference between Store Procedure and User defined Function in Sql Server;

	Store Procedure	User Defined Function
DML Statement	Store procedure supports the DML operations. Like Insert update and Delete.	Its not support DML statements.
Return	Store procedure may or may not return the value	Function must return a value.
Transaction	Transactions allows in Store procedure.	Transactions not allow in user defined functions.
Call by Store Procedure	Store Procedure can call other store procedure and function.	Function cannot call store procedure.
Call from select Statement	Procedures can’t be called from Select statement.	Function can be called from select statement.
Parameters	It Support both input and output Parameters.	It support input but not supports the output parameters.
Exception Handling	Its allow try and catch.	Its not allow try and catch.
Temporary Table	Its allow Temporary table.	Its not allow Temporary table.

Difference between Temporary Table and Table Variable

Below list is major difference between Temporary Table and Table Variable Datatype in Sql Server;

	Temporary Table	Table Variable
Change of Structure	We can change the structure of temporary table after creation of it, we can use DDL statements ALTER, CREATE, DROP	We can’t change the structure of table variable. It doesn’t support DDL Statement like ALTER, CREATE, DROP.
User defined Function	User defined function not allowed temporary table.	Table variables are allowed in User Defined functions.
Transaction	Temporary table support the explicit transactions. .	It not supports the explicit transactions.
Index	Temporary table allow adding explicit indexes after declarations.	Table variables not allow adding explicit indexes after declarations.
Scope	Scope of the Local Temporary Table is in the session in which it created and they are dropped automatically once the session ends and we can also drop them explicitly.	Scope of the Table variable is the Batch or Stored Procedure in which it is declared. and they can’t be dropped explicitly,
Syntax	Creating the temporary Table -- Create Temporary Table CREATE TABLE Employee (EmpId INT, Name VARCHAR(50)) --Insert records INSERT INTO #Employee VALUES(1,'BOB') --Reterive the records SELECT * FROM #Employe --DROP Temporary Table DROP TABLE #Employee	Using Table variables; -- Create Table Variable DECLARE @Employee TABLE (  Id INT,  Name VARCHAR(50)   ) --Insert Two records INSERT INTO @Employee VALUES(1,'BOb') --Reterive the records SELECT * FROM @Employee GO

Difference between Primary Key and Unique Key

Below list is major difference between Primary Key and Unique Key Datatype in Sql Server;

	Primary Key	Unique Key
Declare Limit	Only one columns can be defined in a table	Table can have more than one Unique Key
Indexing	Primary key  defined as in clustered index	Unique Key defined as unique Clustered index.
Nullable	It not allows the Null value.	It allow null value but only one.
Consist Of	PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT	UNIQUE KEY
Syntax	e.g.  a single column PRIMARY KEY column in table; Create Syntax for primary Key: CREATE TABLE dbo.employee ( EmpId INT NOT NULL PRIMARY KEY, Name VARCHAR(50), Address VARCHAR(100), City VARCHAR(50) ) Alter Syntax for primary Key: ALTER TABLE dbo.employee DROP CONSTRAINT PK_EMPLOYEE	e.g.  single column UNIQUE KEY column while creating a table Create Syntax Unique Key; CREATE TABLE dbo.employee ( EmpId INT NOT NULL UNIQUE , Name VARCHAR(50), Address VARCHAR(100), City VARCHAR(50) ) Alter Syntax Unique Key; ALTER TABLE dbo.employee ADD CONSTRAINT UK_EMPLOYEE UNIQUE (EmpId)

Difference Between Char and Varchar in SQL Server

Below list is major difference between Char[n] and varchar[n] Datatype in Sql Server;

Type	char[n]	Varchar[n]
Storage Type	Char is fixed length of datatype. If you declare variable with 20 character length. Like Char(20), it takes 20 bytes regardless storing 1 or 20 character.	varchar is fixed length of datatype. If you declare variable with 20 character length. Like varchhar(20), it takes number of bytes as defined.
Storage Example	DECLARE @Name Char(20) = 'Bob'  SELECT DATALENGTH(@Name) as Result Result ----------- 20	DECLARE @Name VarChar(20) = 'Bob'  SELECT DATALENGTH(@Name) as Result Result ----------- 3
Performance	If your content is fixed size than Char datatype has good to use.	It is performing better than char if variable datatype.
Which should Use	Data can have Unicode characters.	Data doesn’t have any Unicode characters.