TSQL Code formatting tool

Free tool for TSQL code formatting. Added to SSMS

Happy Formatting!!!

TSQL Date Dimension Data Population Script

This post on DateDimension Data Population script. Back to TSQL Days...........

Happy Learning!!!

Interesting TSQL Question

Check the below TSQL Example involving NULL. What is the result for below three queries

Tested on SQL 2012 Setup

Create TABLE Test1

(Id           int,

 Score int not null)

Create TABLE Test2

(Id           int,

 Score2       int not null)

 insert into Test1(Id, Score) VALUES (NULL,10),(10,100),(20,200)

 insert into Test2(Id, Score2) VALUES (NULL,11),(10,110),(20,220)

Case #1 

 Select * from  Test1 JOIN Test2

 ON Test1.Id = Test2.Id

Case #2 

 Select * from  Test1 LEFT OUTER JOIN Test2

 ON Test1.Id = Test2.Id

Case #3

 Select * from  Test1 LEFT OUTER JOIN Test2

 ON Test1.Id = Test2.Id

 ORDER BY Test1.Id DESC

Results are

Happy Learning!!!

Frequently Used Scripts & Notes

TSQL Reusable scripts - bookmarking the same

Tip #1. What is the command to truncate a SQL Server log file?

Tip #2. Settings to Capture Deadlock Trace in SQL Server Logs
DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

Tip #3. Checking SQL Server Version

select SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel'),SERVERPROPERTY('edition')

Tip #4. Creating a custom firefox template

Step 1. Windows -> Run specify below command
firefox.exe -ProfileManager -no-remote

Step 2. Create custom profile

Step 3. Command to run selenium using template
Example:  java -jar selenium-server.jar -firefoxProfileTemplate “<Selenium Profile Directory>”

Usage:  java -jar selenium-server.jar -firefoxProfileTemplate C:\Users\Administrator\AppData\Roaming\Mozilla\Firefox\Profiles\sd88nd1n.FRProfile

Step 4. With Logs captured below is modified steps

Usage: java -jar C:\\SeleniumServer\\selenium-server-standalone-2.25.0.jar -port 5555 > C:\\ReportLogs\\\SeleniumServerStatus.txt 2>&1 -firefoxProfileTemplate "C:\\Users\\Administrator\\AppData\Roaming\\Mozilla\\Firefox\\Profiles\\ku6gbc8j.FRProfile"

References Link1


Happy Learning!!!

TSQL Learning

This post is based on very good reading from Article T-SQL Misconceptions - JOIN ON vs. WHERE. These are new learning's for me


1. For Readability - Refrain from using search arguments in the ON clause, and use the WHERE clause instead
2. When Outer Join is Converted to Inner Join - Adding references to the table in the right side of a JOIN to the WHERE clause will convert the OUTER JOIN to an INNER JOIN. I'm trying out Example Code to reproduce the scenario. This Shows the behavior when Left Join in Query is Converted to Inner Join during Execution.


Step 1 - Create Tables

CREATE TABLE Table1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

CREATE TABLE Table2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

Step 2 - Populate Data

DECLARE @J INT

 SET @J = 1
 WHILE 1 = 1
 BEGIN
 IF @J < 1000
     INSERT INTO Table1(Col2, Col3)
     VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
     INSERT INTO Table2(Col2, Col3)
     VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
     SET @J = @J + 1
     IF @J > 1000
     BREAK;
 END


Step 3 - Run Queries

SELECT

 N1.Col1, N2.Col2
 FROM Table1 N1
 LEFT JOIN Table2 N2
 ON N1.Col1 = N2.Col1
 WHERE N2.Col1 = 100
 

SELECT

 N1.Col1, N2.Col2
 FROM Table1 N1
 LEFT JOIN Table2 N2
 ON N1.Col1 = N2.Col1
 WHERE N1.Col1 = 120


Step 4 - Execution Plan

Happy Learning!!!

TSQL Interesting Questions

Listed below are few TSQL Interesting code snippets and output also provided below the examples. Output I observed in SQL 2008 R2 version.

Example #1
declare @i int
select @i = -5
select +@i
Output: -5

Example #2
declare @i varchar
select @i = 'This is a test'
select @i
Output: T

Example #3
declare @val char(30)
set @val = 'SSc is cool'
select len(@val)
Output: 11

Example #4
declare @val as float, @val1 as float
set @val = 2.0
set @val1 = 1.4
select @val1+@val as sum
Output: 3.4 

Example #5. Minimum of each coulmn

create table #i ( i int, j int, k int)
insert #i values(1, 2, 3)
insert #i values(1, 3, 2)
insert #i values(2, 1, 3)
insert #i values(2, 3, 1)
insert #i values(3, 1, 2)
insert #i values(3, 2, 1)
insert #i values(1, 2, 1)
insert #i values(1, 1, 1)
insert #i values(1, 0, 1)

select (case when (i <= j and i <= k) then i when j <= k then j else k end)
from #i

Example #6. Greatest value of 3 columns. Here's an example of what you want using a derived table

declare @t table(id int, val1 int,val2 int,val3 int)
insert into @t(id,val1,val2,val3) values(1,100,200,300)
insert into @t(id,val1,val2,val3) values(2,300,400,500)
insert into @t(id,val1,val2,val3) values(3,600,700,800)

select id,max(vals)
from
(
   select id,val1
   from @t
union all
   select id,val2
   from @t
union all
   select id,val3
   from @t
) X(id,vals)
group by id

Example #7. Maxof All 3 columns

select max(vals)
from
(
   select A
   from #min
union all
   select B
   from #min
union all
   select C
   from #min
) X(vals)

Thanks to authors of this problems. I am not sure where I read this 'C' Programming Style Problems. I found it interesting.

Basics - Employee manager Legacy Interview Question

Question - Query to Display Employee and their Manager names. Table details provided below.

use tempdb
--STEP1
CREATE TABLE Employee
(ID INT,
Name VARCHAR(50),
ManagerId INT)

--STEP2
INSERT INTO Employee(ID,Name,ManagerId)
VALUES
(1,'Siva',NULL),
(2,'Raj',1),
(3,'Raja',2),
(4,'Santosh',3),
(5,'Santosh Raj',4)

--STEP 3
SELECT Distinct E.ID as 'EmployeeID', E.Name as 'EmployeeName', M.ID as 'ManagerId', M.Name as 'Managername' FROM Employee E LEFT JOIN Employee M
ON E.ManagerId = M.Id

I am trying on SQL 2008 R2..

I wanted to learn data structures and post some interesting interview questions. I did attend course few months back. Whatever I could grab and I learnt I will post it soon....All only 'C' programs :).....

Question - Write a Query to return Employeee and Last Maximum Salary

CREATE TABLE EmpSal
(
Empid INT,
MonthofPay VARCHAR(20),
Salary Money
)

INSERT INTO EmpSal(Empid,MonthofPay,Salary)
VALUES(10,'June',15000),
(10,'July',18000),
(10,'April',21000),
(10,'August',24000),
(11,'June',25000),
(11,'July',28000),
(11,'April',31000),
(12,'August',44000),
(12,'June',55000),
(12,'July',18000),
(12,'April',11000),
(12,'August',14000)

SELECT E1.* FROM EmpSal E1 JOIN

(
         SELECT Empid, MAX(Salary) as Salary
         FROM EmpSal
         GROUP BY Empid
) E2
ON E1.Empid = E2.Empid
AND E1.Salary = E2.Salary

2. Write a query to return third max salary

SELECT Top 1 Salary, * From EmpSal E1
WHERE E1.Salary IN
       (
            SELECT Top 3 Salary
            FROM EmpSal
            Order by Salary Desc
       )
Order by E1.Salary Asc

Primenumber generation in TSQL

Basics - Deleting Duplicate Entries using ROW_NUMBER() Function

ROW_NUMBER() - Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied (As mentioned in MSDN)

IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME ='TEST')

DROP TABLE TEST
GO
--STEP1
CREATE TABLE TEST
(ID INT,
NAME VARCHAR(20))
GO

--STEP 2
INSERT INTO TEST (ID,NAME)
VALUES (1,'Raj')
GO
INSERT INTO TEST (ID,NAME)
VALUES (1,'Raj')
GO
INSERT INTO TEST (ID,NAME)
VALUES (2,'Ram')
GO
INSERT INTO TEST (ID,NAME)
VALUES (2,'Ram')
GO
SELECT * FROM TEST
GO

--STEP 3
WITH [CTE DUPLICATE] AS
(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID DESC),
Id,NAME
FROM TEST
)
DELETE FROM [CTE DUPLICATE]
OUTPUT DELETED.*
WHERE RN > 1;
GO

--STEP 4
SELECT * FROM TEST

Example - Use of Cross Apply Operator

We will look at simple example using CROSS Apply operator. Using Cross Apply Operator to JOIN table returned by function. Example below lists an example step by step

use tempdb

DROP TABLE Employee
DROP TABLE ADDRESS

--STEP 1
CREATE TABLE Employee
(
     NAME VARCHAR(20),
     Id INT Primary Key
)

--STEP 2

CREATE TABLE ADDRESS
(
   Id INT Foreign Key References Employee(Id),
   Location VARCHAR(100),
   Isactive bit
)

--STEP 3

INSERT INTO Employee (NAME,Id)
VALUES ('Ram',1)
INSERT INTO Employee (NAME,Id)
VALUES ('Sri',2)

--STEP 4

INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (1,'Chennai',1)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (1,'Bangalore',0)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (2,'Bangalore',1)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (2,'Delhi',0)

--STEP 5

SELECT * FROM ADDRESS

--STEP 6
CREATE Function dbo.getaddress (@id int)
RETURNS
@ADDRESS TABLE
(
   Id int,
   location varchar(100)
)
AS
BEGIN
   IF ISNULL(@id,0) = 0
     BEGIN
       RETURN
     END
   INSERT INTO @ADDRESS(Id, location)
   SELECT ID, Location
   FROM ADDRESS
   WHERE Isactive = 1
   AND Id = @id
RETURN
END

--STEP 7

--Function and Cross Apply
SET STATISTICS IO ON
SELECT * FROM
dbo.Employee T1 CROSS APPLY
dbo.getaddress(T1.Id)

--Table '#1CF15040'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'Employee'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT * FROM

dbo.Employee T1 JOIN ADDRESS AD
ON T1.Id = AD.Id
WHERE AD.Isactive = 1
--Table 'Employee'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'ADDRESS'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SCAN Count is Zero for employee table. Reason Here. Both inputs to nested loop are "SEEK"s, meaning you should usually see zero scan count.

Reference - Interpreting IO Statistics

To Touch on Basics Again

• Scalar Functions - Returns Single Variable
• Inline Table Value Function - Returns Table
• MultiStatement TVF - Return Table as defined in Schema

Good link on Difference between Functions and Stored Procedures Pinal Article - link

Happy Reading!!

Simple TSQL Exercise

Question #1
Given a Table, Lookup based on Priority and return the value of action based on it

--Priotitylist
1. Match for PriorityA, PriorityB
2. Match for PriorityA, *
3. Match for *, PriorityB
4. Match for *, *

--STEP 1
CREATE TABLE TestPriority
(
PriorityA CHAR(5) NOT NULL,
PriorityB CHAR(5) NOT NULL,
Action BIT NOT NULL
)

--STEP2
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('AA','BB',1)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('AA','*',0)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('*','BB',1)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('*','*',0)

--STEP 3
DECLARE @PriorityA CHAR(5)
DECLARE @PriorityB CHAR(5)
SET @PriorityA = '*'
SET @PriorityB = 'BB'

SELECT Top 1 Action
FROM
(
SELECT ACTION, Priority =
CASE WHEN PriorityA = @PriorityA AND PriorityB = @PriorityB THEN 1
WHEN PriorityA = @PriorityA AND PriorityB = '*' THEN 2
WHEN PriorityA = '*' AND PriorityB = @PriorityB THEN 3
WHEN PriorityA = '*' AND PriorityB = '*' THEN 4
END
FROM TestPriority
) AS TESTResult
WHERE Priority IS NOT NULL
Order by Priority ASC

Question #2
You Have a Customer And Interest Table. Write a Query to Calculate Interest based on below conditions

Condition
1. When a match is found use the interest value
2. When no match found use default value '*'

--STEP 1
CREATE TABLE INTEREST
(
Region VARCHAR(5),
Rate INT
)

--STEP 2
INSERT INTO INTEREST(Region,Rate)
VALUES('CA',5),('US',10),('FR',20)

INSERT INTO INTEREST(Region,Rate)
VALUES('*',25)

--STEP 3
CREATE TABLE Customer
(
Name VARCHAR(20),
Amount INT,
Region VARCHAR(5)
)

--STEP 4
INSERT INTO Customer(Name,Amount,Region)
VALUES('Raj',10000,'CA')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Raja',10200,'SA')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Raa',10200,'FR')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Ram',10200,'IN')

SELECT * FROM Customer

--STEP 5
SELECT C.Name, C.Amount*I.Rate/100, C.Amount, C.Region FROM
INTEREST I JOIN Customer C
ON ((I.Region = C.Region) OR (I.Region = CASE WHEN C.Region NOT IN (SELECT Region FROM INTEREST) THEN '*' END))

Deleting Huge Recordset from Table in batches

While deleting a Dataset < @consition from a huge table (ex-millions of records we would get transaction log full error). The best practice is to do a batch delete. A good example is provided here One Way is mentioned here recurring step

I would recommend the solution mentioned here

--CREATE Testt Table
create table testt
(id int identity primary key not null
,name char(10)
)

--Insert Entries
declare @i int
set @i=0
while (@i <10000)
  begin
      insert into testt (name)
      select 'name'+CONVERT(char,@i)
      set @i=@i+1 end

--Delete Entries
set rowcount 5000
while 1=1
  begin
      delete from testt where id > 1000
      if @@ROWCOUNT = 0
      break;
  end
set rowcount 0

select count(1) from testt (nolock)
--1000

Also additional benefit is when batches are committed log file portion which was used can be reused as data is committed already. Plus only a range lock would be placed for the portion of records (range lock), Possibilities of escalating to a table lock is less.

Lock escalaltion is triggered when A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index. source here

Performing batched updates

While BCP for faster inserts the database recovery model of the target table must be either Simple or Bulk Logged. From Msdn link. Anothere Reference link

DELETE TOP x rows avoiding a table scan
Update Record in Batches - TSQL coding patterns I
Gradually Deleting Data in SQL Server

Happy Learning!!!