What is difference between Response.Write() and Response.Output.Write()

Response.Output.Write() allows us to write formatted output.

INTERSECT in SQL SERVER

INTERSECT

       Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

Simply its returns common distinct values from right and left side of  INTERSECT operator.

--CREATE TEMP TABLE

create table #tblsample (ProductId tinyint)

create table #tblsample2 (ProductId tinyint)

--insert values to tbl 1

insert into #tblsample values (1)

insert into #tblsample values (2)

insert into #tblsample values (3)

insert into #tblsample values (4)

--insert values to tbl 2

insert into #tblsample2 values (1)

--SELECT

select * from #tblsample

select * from #tblsample2

--USE INTERSECT

select * from #tblsample

INTERSECT

select * from #tblsample2 

--insert duplicate values to tbl2

insert into #tblsample2 values (2)

insert into #tblsample2 values (2)

--USE INTERSECT

select * from #tblsample

INTERSECT

select * from #tblsample2 

Above query returns common distinct values.

EXCEPT vs NOT IN in SQL SERVER

“EXCEPT” operator was introduced in SQL SERVER 2005. 
This operator used to achieve Distinct and Not In queries.
EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table.
On the other hand “NOT IN” will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result. 

Example of EXCEPT vs NOT IN in SQL SERVER

create table #tblsample (ProductId tinyint)

create table #tblsample2 (ProductId tinyint)

--insert values to tbl 1

insert into #tblsample values (1)

insert into #tblsample values (2)

insert into #tblsample values (3)

insert into #tblsample values (4)

--insert values to tbl 2

insert into #tblsample2 values (1)

--SELECT

select * from #tblsample

select * from #tblsample2

--USE EXCEPT

select * from #tblsample

except

select * from #tblsample2

--USE NOT IN

select * from #tblsample

where productid NOT IN(

select * from #tblsample2)

-- INSERT DUBLICATE VALUE

insert into #tblsample values (1)

insert into #tblsample values (2)

--SELECT

select * from #tblsample

--USE EXCEPT

PRINT 'EXCEPT RETURNS DISTINCT VALUES'

select * from #tblsample except select * from #tblsample2

--USE NOT IN

PRINT 'NOT IN RETURNS DUPLICATE VALUES IF ANY'

select * from #tblsample where productid NOT IN(select * from #tblsample2)

--USE DISTINCT + NOT IN = EXCEPT

select * from #tblsample

except

select productid from #tblsample2

--DROP TABLE

DROP TABLE #tblsample

DROP TABLE #tblsample2

CodeProject

Insert a record into two tables without using trigger

CREATE TABLE TestTable (ID INT, NAME VARCHAR(100))

CREATE TABLE HistoryTable (ID INT, NAME VARCHAR(100))

INSERT TestTable (ID, NAME)

OUTPUT Inserted.ID, Inserted.NAME INTO HistoryTable

VALUES (1,'Value1')

Select * from TestTable

Select * from HistoryTable


We have used OUTPUT clause to achieve above result without using trigger.

What is OUTPUT clause

OUTPUT clause returns a copy of the data that you have inserted into or deleted from your tables.
You can return that data to a table variable, a temporary or permanent table.

-------------------------------------------------------------------------------
Additionally want to know this:

Difference between LEN and Data Length in SQL

Count Columns in a table

DBCC stands for

DBCC stands for database consistency checker.

Difference between LEN and DATALENGTH

LEN and DATALENGTH are returns the count of given value. LEN is count the length of characters,But DATALENGTH count the bytes of the character.

DECLARE @v NVARCHAR(50)

SELECT @v ='Vinoth'

SELECT LEN(@v) AS[LEN],DATALENGTH(@v) AS [DATALENGTH]

LEN         DATALENGTH
----------- -----------
6           12
(1 row(s) affected)

 ----------------------------------------------------------------------------

LEN doesn't count trailing spaces.

SELECT DATALENGTH('Vinoth ') AS [DATALENGTH], LEN('Vinoth ') AS [LEN]

DATALENGTH  LEN
-----------      -----------
7                  6
(1 row(s) affected)