use for communicating between
Web
API Rest full
|
ASP.Net
WCF Rest Support
|
|
Support Protocol
|
It support only HTTP protocol.
|
It support multiple protocol such as
HTTP, UDP, TCP.
|
Support WS* Standard
|
It’s not supporting Transaction or reliable
messaging.
|
It support WS* standard like Transaction,
Reliable Messaging. Message security etc.
|
SOAP Support
|
No
|
WCF was primarily designed for
SOAP-based XML messages,
|
Availability
|
Ship with .Net framework but available as open source.
|
Ship with .Net Framework.
|
Configuration and Attribute
|
No need of much configuration to use
it.
|
There is hell lot of configuration
required to use it and overuse of attributes.
|
Message Exchange Pattern
|
It support Request/response only.
|
It support One Way, Request-Reply, Duplex message Exchange pattern.
|
Table
|
View
|
Table contains Data.
|
View doesn’t contains data.
|
Table is physically existence.
|
View doesn’t physically existence. It’s like virtual table.
|
You can perform insert, update, delete and Select for it. |
|
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 |
|
|
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
|
DELETE statement lock row, each row in the table is locked for
deletion.
|
TRUNCATE always locks the table and page but not each row.
|
|
|
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)
|
|
|
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.
|
|
|
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
|
