Sunday, 27 September 2015

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)

Saturday, 26 September 2015

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.



Others

You will like to read it