IDENTITY_INSERT Scope level

Some time we need to insert in the identity field also but what will be the impact if we want to Allow theĀ  identity insert, see the below example:

Create a Test Table :
————————————–

CREATE TABLE sample(
id int IDENTITY(1,1)
,name VARCHAR(100)
,address VARCHAR(100)
,phoneNo VARCHAR(15)
)

In One Session, I am going to Allow the Identity Insert
————————————————————————–

SET IDENTITY_INSERT dbo.sample ON;

INSERT INTO sample(id,name,address,phoneNo)
SELECT 10,'user-2','address-2','014002555-2'


SELECT * FROM sample

In another session i am going to insert normally without ID value:
——————————————————————-
INSERT INTO dbo.sample(name,address,phoneNo)
SELECT 'user','address','014002555'

SELECT * FROM sample

—————————-

So the Conclusion is IDENTITY_INSERT will be Enabled on Session Level only. You can insert with identity value from one session and another session will be working normally with auto identity value.

MSSQL Cross-Tab Query

Example Script for MSSQL Cross-Tab Query

Create table #Table
(
yearofJoining int,
EmpId int,
Deptid int
)
GO


insert into #Table select 1990,1,1
insert into #Table select 1991,2,2
insert into #Table select 1990,3,4
insert into #Table select 1991,4,2
insert into #Table select 1990,5,1
insert into #Table select 1990,6,3
insert into #Table select 1992,7,3
insert into #Table select 1990,8,4
insert into #Table select 1993,9,1
insert into #Table select 1994,10,2
insert into #Table select 1990,11,3
insert into #Table select 1995,12,3
insert into #Table select 1995,14,3
insert into #Table select 1995,15,3
insert into #Table select 1995,16,6
GO

--Original Cross Tab query
select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #Table where deptid in(1,2,3)
group by Yearofjoining

Out put:

YearofJoining Department-1 Department-2 Department-3
------------- ------------ ------------ ------------
1990 2 0 2
1991 0 2 0
1992 0 0 1
1993 1 0 0
1994 0 1 0
1995 0 0 3

PIVOT Example:


SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #Table) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining

MSSQL Case

Here are some example of MSSQL Cases:

USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Other way:

USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO

MSSQL EXISTS and IF EXISTS

Using NULL in a subquery to still return a result set:

USE AdventureWorks ;
GO
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;

Comparing queries by using EXISTS and IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO

Usage of IN :

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO

Comparing queries by using EXISTS and = ANY


USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store s
WHERE EXISTS
(SELECT *
FROM Purchasing.Vendor v
WHERE s.Name = v.Name) ;
GO

Usage of ANY:

USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store s
WHERE s.Name = ANY
(SELECT v.Name
FROM Purchasing.Vendor v ) ;
GO

Comparing queries by using EXISTS and IN

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EXISTS
(SELECT *
FROM HumanResources.Department d
WHERE e.DepartmentID = d.DepartmentID
AND d.Name LIKE 'P%');
GO

Usage of IN :

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE DepartmentID IN
(SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%');
GO

Using NOT EXISTS

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department d
WHERE e.DepartmentID = d.DepartmentID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
GO

Advance Usage of Exists

If exists(
SELECT id FROM Table1 where price > = 1000
)
begin

SELECT '>= than 10000' as MSG
print '> than 10000' -- OR you can print as out put
return;


end

Find All SQL Server Instance Running in Local Network

How to list all SQL Server Instance Running in Local Network:
Some time you need to see the List of other running SQL server on your network. It is so simple and easy to call from other application also.

See the example:

-- ######### Query
EXEC master..xp_cmdshell 'osql -L'

-- ############ Result

NULL
Servers:
    (local)
    MAIL_SERVER
    RAGHU
    RAGHU\SQLEXPRESS
    SERVER1
NULL

If you want to get the same list from Command prompt then:

C:\> osql -L

it will show you the same list.