Be careful if you are using CTE!

Previously I wrote a post about CTE (Common Table Expressions). And Common Table Expressions are valid for a single statement only. But single statement can use many CTEs. And statement also should be within same batch.

USE AdventureWorks2012
GO

--Defining CTE
WITH ctePerson 
AS
(
    SELECT * 
    FROM Person.Person
)
--First Statement will run 
SELECT FirstName FROM ctePerson 
--Second statement will occur an error
SELECT FirstName FROM ctePerson

And in my previous post I have mentioned about many CTEs for one statement. And there is another important thing that is be careful when you are naming CTEs. Take a look on below example.

USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

CREATE TABLE cteSampletbl2
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(2,'Preethi'),
(3,'Dinesh'),
(4,'Angelo')
GO

INSERT INTO cteSampletbl2 VALUES
(1,'Shamil'),
(2,'Hasitha'),
(3,'Abhinandana'),
(4,'Susantha')
GO

/*
    This CTE is defined with existing table name without any error
*/
WITH cteSampletbl2
AS(
    SELECT * FROM cteSampletbl1
)
--This statement uses CTE
SELECT * FROM cteSampletbl2
--This statement uses existing table
SELECT * FROM cteSampletbl2

--Dropping tables
DROP TABLE cteSampletbl1
DROP TABLE cteSampletbl2
GO

Below example it lets us to create a CTE with a name of existing table without any warnings or errors. And it gives priority to CTE in first statement. Then it uses existing table in second statement. So it would be a best practice to use a separate prefix for CTEs. Then take a look into next sample which is taken from Dinesh’s presentation for SQL Server Universe Group.

USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(2,'Preethi'),
(3,'Dinesh'),
(4,'Angelo')
GO

--This CTE got error
WITH cteSampletbl1
AS(
    --Same name could not be used as name and in CTE
    SELECT * FROM cteSampletbl1
)
SELECT * FROM cteSampletbl1

--This CTE don’t have any error
WITH cteSampletbl1
AS(
    --full name of object should provide
    SELECT * FROM dbo.cteSampletbl1
)
SELECT * FROM cteSampletbl1

--Dropping tables
DROP TABLE cteSampletbl1
GO

You can’t use same table name as the name of the CTE and inside the CTE.

Common Table Expressions (CTE)

In Stored procedures and in our SQL Statement sometimes we need to use same query again and again. In that case you can use CTE.

USE AdventureWorks2012
GO

--Defining CTE
WITH Empl
AS
(
    SELECT * FROM HumanResources.Employee
)

--Using CTE
SELECT Empl.* FROM Empl
GO

And you can use several CTE’s in one statement.

USE AdventureWorks2012 GO

–Defining CTE

WITH Empl AS ( SELECT * FROM HumanResources.Employee ),

Pers AS ( SELECT * FROM Person.Person )

–Using CTE

SELECT Empl.*, Pers.*

FROM Empl INNER JOIN Pers

ON Empl.BusinessEntityID = Pers.BusinessEntityID

GO

Updated : At the very begining I though that CTE were reusable again and again inside a one batch. But it was a myth. I realized it after posting this post, but forgot to remove that today I removed that.

Ranking in T-SQL

In Queries we need to rank and number records. SQL Server gives you few functions to rank your records.

RANK

Rank we can use  to rank data in normal way. In this ranking if we have two 1 next one will have rank 3. 

Name Marks Rank
John 75 1
Mark 75 1
Steve 64 3
Gates 54 4

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Ranking data over Score
SELECT *, RANK() OVER (ORDER BY [Score] DESC) AS [RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

This ranks based on the column that you specify by OVER.

DENSE_RANK

Dense Rank is not missing ranks like in normal rank function. It has continuity over ranking.

Name Marks Dense Rank
John 75 1
Mark 75 1
Steve 64 2
Gates 54 3

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Ranking data over Score
SELECT *, DENSE_RANK() OVER (ORDER BY [Score] DESC) AS [DENSE RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

ROW NUMBER

Row Number don’t worry much about ranking it just give an incremental number to row. It’s simple.

Name Marks Row Number
John 75 1
Mark 75 2
Steve 64 3
Gates 54 4

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Numbering data over Score
SELECT *, ROW_NUMBER() OVER (ORDER BY [Score] DESC) AS [ROW NUMBER] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

NTile

NTile will separate your recode into the number that you parse. If you want to group your results into 3 groups you can use NTILE(3). If you want to divide your results into 7 you can use NTILE(7).

Name Mark NTile(3)
Dedunu 89 1
Dhananjaya 85 1
Hasitha 75 2
Shamil 74 2
Sanjana 65 3
Anuradha 55 3

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(3) over Score
SELECT *, NTILE(3) OVER (ORDER BY [Score] DESC) AS [NTile(3)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

If you are going to group them into 4, you will have this result. It always try to round to the lower numbers.

Name Mark NTile(4)
Dedunu 89 1
Dhananjaya 85 1
Hasitha 75 2
Shamil 74 2
Sanjana 65 3
Anuradha 55 4

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(4) over Score
SELECT *, NTILE(4) OVER (ORDER BY [Score] DESC) AS [NTile(4)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

Then if you are going to group odd numbered records by even or even numbered records by odd, first tile will have an extra record.

Name Mark NTile(5)
Dedunu 89 1
Dhananjaya 85 1
Hasitha 75 2
Shamil 74 3
Sanjana 65 4
Anuradha 55 5

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(5) over Score
SELECT *, NTILE(5) OVER (ORDER BY [Score] DESC) AS [NTile(5)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

If you are trying to group records by more than records number or same number it will act like ROW_NUMBER function. 

Name Mark NTile(>=6)
Dedunu 89 1
Dhananjaya 85 2
Hasitha 75 3
Shamil 74 4
Sanjana 65 5
Anuradha 55 6

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(6) data over Score
SELECT *, NTILE(6) OVER (ORDER BY [Score] DESC) AS [NTile(6)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(8) over Score
SELECT *, NTILE(8) OVER (ORDER BY [Score] DESC) AS [NTile(8)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

PARTITION BY

Lets move to Partition By clause. If you want to categorize your ranks into any other factor, you can use PARTITION BY clause. It lets you to have separate rankings on a factor that you want.

Name Subject Mark Rank
Hasitha Eng 75 1
Anuradha Eng 55 2
Dedunu Math 89 1
Dhananjaya Math 85 2
Sanjana Math 65 3
Shamil Sci 74 1

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Subject] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Subject], [Score]) 
    VALUES ('Dedunu','Math',89),('Dhananjaya','Math',85),('Hasitha','Eng',75),('Shamil','Sci',74),('Sanjana','Math',65),('Anuradha','Eng',55)
GO

--Partitioning rank by Suject, and ranking over Score
SELECT *, RANK() OVER (PARTITION BY [Subject] ORDER BY [Score] DESC) AS [RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

Delete using another Table

In this example I create a temp table on AdventureWorks2012 Database, and first I load all the data in HumanResources.Employee to the temp table. Then I delete data using HumanResources.Employee. This command will delete all the records in Tmp which has Gender = ‘M’ in HumanResources.Employee table.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--DELETE Using another table
DELETE Tmp 
    FROM [HumanResources].[Employee] AS HE, Tmp
    WHERE Tmp.BusinessEntityID = HE.BusinessEntityID
    AND HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

You can use inner or outer join to combine those data.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--DELETE Using another table
DELETE Tmp 
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Tmp
    ON Tmp.BusinessEntityID = HE.BusinessEntityID
    WHERE HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

Both of those statements gives same results. It will delete all the male employees.

SELECT INTO

My previous blog post was about using insert in many ways. You can use SELECT INTO to add data from existing table to a new table. In this statement SQL Server will create a table for you. You just want to write the query and add INTO clause to the statement. Rest will done by SQL Server for you.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee to non existing table
SELECT  [BusinessEntityID],
        [LoginID],
        [OrganizationNode],
        [JobTitle],
        [BirthDate],
        [MaritalStatus],
        [Gender] 
    INTO Tmp
    FROM HumanResources.Employee
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

If you are trying to add data into a existing table by using this INTO clause, you will have a error. You can join two table and add data into new table too.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  HE.[BusinessEntityID],
        HE.[LoginID],
        HE.[Gender],
        PP.[FirstName],
        PP.[NameStyle]
    INTO Tmp
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Person.Person AS PP
    ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

And you can use conditions as well.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  HE.[BusinessEntityID],
        HE.[LoginID],
        HE.[Gender],
        PP.[FirstName],
        PP.[NameStyle]
    INTO Tmp
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Person.Person AS PP
    ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
    WHERE HE.[BusinessEntityID] BETWEEN 70 AND 80
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

When you are joining two tables consider about the column names too. If you are using same name to two or more columns SQL Server will occur error. Below code will generate such a error.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  HE.[BusinessEntityID],
        HE.[LoginID],
        HE.[Gender],
        PP.[BusinessEntityID],
        PP.[FirstName],
        PP.[NameStyle]
    INTO Tmp
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Person.Person AS PP
    ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

Live Writer and T-SQL

Today I wanted to paste few statements on Live Writer which were coded on SQL Server Management Studio 2012. But when I paste the code into Live Writer I got disappointed about the look. Codes were awesome on SMSS 2012 with colors. Then I wanted to find a way to insert those code with Visual Studio formatting. I found few plugins but they were not cool. Then I found this blog.

http://blog.hoegaerden.be/2010/01/15/windows-live-writer-paste-code-plug-in/

He had found a nice plugin. I downloaded that and Installed that. Now I use that It is cool plugin. No windows just a plugin.

http://plugins.live.com/writer/detail/paste-from-visual-studio

Here’s the link for that plugin. Enjoy Blogging!!!

Insert in T-SQL

I’m still a beginner in SQL Server. Then I  will blog about so many basic things in SQL Server. Inserting is very important Data Manipulation Statement. So lets see how to use INSERT Statement in different ways.

USE tempdb
GO

--Creating Table
CREATE TABLE tmpTable
(
    [id] int NOT NULL,
    [name] varchar(100) NULL,
    [address] varchar(1000) NULL
)
GO

--Inserting Data
INSERT INTO tmpTable([id],[name],[address]) VALUES(1, 'John', '123, qwerty')
GO

--Inserting Data by Changing order column
INSERT INTO tmpTable([name],[id],[address]) VALUES('Mark',2, '234, qwerty')
GO

--Inserting Data without specifying columns
INSERT INTO tmpTable VALUES(3,'Steve', '454, qwerty')
GO

--Deleting temp table
DROP TABLE tmpTable
GO

Above statements are the basic inserting methods. But its recommended to specify columns. If you want to change the schema structure later It will not affect your code if you have used INSERT with specified columns. And if you don’t specify columns be careful because you have to provide data for every column.

USE tempdb
GO

--Creating Table
CREATE TABLE tmpTable
(
    [id] int NOT NULL,
    [name] varchar(100) NULL,
    [address] varchar(1000) NULL
)
GO

/*
    If you don't specify column names you have to 
    insert data to every columns you can't skip
    columns
*/

--This cause a error 'Column name or number of supplied values does not match table definition.'
INSERT INTO tmpTable VALUES(4,'Gates' )
GO

--Dropping the temp table
DROP TABLE tmpTable
GO

Playing with DEFAULT Values

Sometimes we need to work with DEFAULT values. Then if you want to let SQL Server to decide values you can code like this.

USE tempdb
GO

--Creating Table
CREATE TABLE tmpTable
(
    [name] varchar(100) DEFAULT ('Name'),
    [address] varchar(1000) DEFAULT ('Address')
)
GO

--You can let sql server to add default values by mentioning default 
INSERT INTO tmpTable VALUES('John',default )
GO

INSERT INTO tmpTable VALUES(default,'123 qwerty')
GO

--If you like you can give default values to all the columns too.
INSERT INTO tmpTable VALUES(default,default)
GO

--You can ignore values for that column. Then you have to specify column name
INSERT INTO tmpTable([name]) VALUES('Mark')
GO

--Retrieving data
SELECT * FROM tmpTable
GO

--Dropping the temp table
DROP TABLE tmpTable
GO

Inserting data from another table

In some cases we need to insert data from one table to another. Then we can use INSERT statement to copy data from another table.

USE [AdventureWorks2012]
GO

--Creating a temp table
CREATE TABLE Tmp(
    [BusinessEntityID] [int] NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL,
    [OrganizationNode] [hierarchyid] NULL,
    [JobTitle] [nvarchar](50) NOT NULL,
    [BirthDate] [date] NOT NULL,
    [MaritalStatus] [nchar](1) NOT NULL,
    [Gender] [nchar](1) NOT NULL,
)
GO

--Inserting data from another table
INSERT INTO Tmp
    (
        [BusinessEntityID],    
        [LoginID],
        [OrganizationNode],
        [JobTitle],
        [BirthDate],
        [MaritalStatus],
        [Gender]    
    ) 
    --Selecting data from HumanResources.Employee
    SELECT 
        [BusinessEntityID],
        [LoginID],
        [OrganizationNode],
        [JobTitle],
        [BirthDate],
        [MaritalStatus],
        [Gender]
    FROM HumanResources.Employee
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO