Author Archives: Riteshshah

About Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

Correlated Subquery – SQL-Server

Correlated Subquery in MS-SQL Server:

I am writing this article by assuming that you are very well aware with sub-query concept as this is little bit ahead than sub-query. Before we start technically, if you will observe the name “CO-Related subquery”, you will get the concept that this is something highly related to each other and yes, you are absolutely right. Correlated sub-query (also known as repeating subquery) is depend on the main outer query, if you will run it without outer query, you will be greeted with error, while in subquery, if you will run sub-query without outer query, it will give you results set.

MICROSOFT’s definition for Correlated sub-query in its books online:

Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

Well, enough theory, right????? Now let’s move towards some practical stuff.

We will see this with two table related to cricket match. J

Create one table with cricketer’s personal details with below given query.

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

Now, we will insert few records in it.

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)

Once you are done with this, please, create one table for score board as follow.

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

Now, it is time to insert few records in score board table.

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)

Now, If you want to see the tournament (match name) with cricketers name, who made run below than average run with correlated subquery.

SELECT C.Name,p.MatchName,p.Run

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

WHERE

run<(SELECT AVG(Run) from PersonalScore ps where p.matchname=PS.matchname)

First subquery will calculate the Average of runs and than it will execute every time outer query get new “MatchName”. Every single “MatchName” from outer query will be passed to subquery and it return the results based on the situation.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records, could be done by several ways so this is another way to do the same.
You can refer earlier article by following link.
http://blog.extreme-advice.com/2009/02/28/select-delete-duplicate-records-sql-server/
As this is going to be done by CTE, You can get more information about CTE by following link.
Once you create table given in above link, you can use the below given query for selecting or deleting records.
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
Select * from DeleteDuplicateRecords where RowNum>1
Or
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
DELETE from DeleteDuplicateRecords where RowNum>1
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CTE (Common Table Expression) SQL-Server 2005

One of the beautiful features of MS SQL-Server 2005 is CTE and it is mainly used for recursive query. In recursive query, query executes itself; it is the same concept we used to use in C or C# etc programming language for recursive function. In real world we often need recursive hierarchical data for list of category and subcategory resides in one single table. Let’s have a look at it.

Step 1:

Simply create one table.

USE AdventureWorks

GO

Create Table VehicleCategory

(

ID Int Constraint PK_VehicleCategoryID Primary Key,

BikeCategory VarChar(100),

ParentID Int Constraint FK_VehicleCategory_ParentID References VehicleCategory(ID),

Description varchar(50)

)

GO

Step 2:

Insert few records in above created table:

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(1,‘Bike’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(2,‘Scooter’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(3,‘Yamaha RX 100’,1,‘125 CC bike of Yamaha’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(4,‘Hero Honda CBZ’,1,‘150 CC bike of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(5,‘Honda Activa’,2,‘125 CC Scooter of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(6,‘TVS Scooty’,2,’75 CC Scooter of TVS’)

Step 3:

Now, if I want all the category and subcategory of “Bike” than what I suppose to do? There may be different ways to fulfill this requirement but I would like to go for new feature introduce in SQL-Server 2005, which is CTE (Common Table Expression)

WITH cte_VehicleCategory AS

(

SELECT ID,BikeCategory,ParentID,Description FROM VehicleCategory Where ID=1

UNION ALL

SELECT v.ID,v.BikeCategory,v.ParentID,v.Description FROM VehicleCategory AS v

INNER JOIN

cte_VehicleCategory AS cv ON v.ParentID=cv.ID

)

Select * from cte_VehicleCategory

Let’s study little bit more about how this magical code worked???

Our CTE name is cte_VehicleCategory, which will get its base records from the first query above the UNION ALL. It will iterate to seek ID’s value in parentid of query below the UNION ALL. Now, as you know that this is recursive query so if you want to create infinite query ;) than change statement after “INNER JOIN” to “cte_VehicleCategory AS cv ON v.ID=cv.ID”

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Select-Delete-Duplicate-Records-SQL-Server

Sometime we require finding duplicate records in our table. There are several ways to it and I will keep posting separate article for all ways to do the same task. Here is one very short and sweet solution by simple SQL query. Have a look at it.

Well, I am going to create one simple table in AdventureWorks database.

use adventureworks

GO

Create Table SelectDuplicate

(

ID int identity(1,1) not null,

Fname varchar(10) not null,

Lname varchar(10) not null,

City varchar(10) not null

)

Now, this is a time to enter some records in just created table above.

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘Ahmedabad’)

INSERT INTO SelectDuplicate VALUES(‘Avi’,‘Sagiv’,‘Edison’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Parsipenny’)

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘WestField’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Ahmedabad’)

So, here is the heart of article below.

SELECT * FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

As I mentioned in first paragraph of article as well, there are few different ways to do the same thing which will be explained in coming articles.

Of course, you can delete these duplicate records as well, if you wish. All you need to do is change first “SELECT *” to “DELETE” as below given code.

DELETE FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Email sending script from C#

Sending an email from C# script is quite easy thing. Most popular two ways to send an email from C# scripts are as follows:
1.)
public string sendingMail(string strFrom, string strTo, string strSubject, string strMessage)
{
MailMessage myMessage = new MailMessage(new MailAddress(strFrom), new MailAddress(strTo));
myMessage.IsBodyHtml = true;
myMessage.Priority = MailPriority.Normal;
myMessage.Body = strMessage;
myMessage.Subject = strSubject;
SmtpClient client = new SmtpClient(““, 25);
try
{
client.Send(myMessage);
}
catch(Exception ex)
{
return ex.ToString();
}
return “Message Has Been Sent”;
}

2.)
public string AuthenticatedMailSend(string mailTo, string Subject, string MessageBody)
{
System.Net.Mail.MailMessage email = new MailMessage(“TEST@test.com”, mailTo);
email.Subject = Subject;
email.IsBodyHtml = true;
email.Body = MessageBody;

System.Net.Mail.SmtpClient mailClient = new SmtpClient();
System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential(““, ““);
mailClient.Host = ““;
mailClient.Port = 25;
mailClient.UseDefaultCredentials = false;
mailClient.Credentials = basicAuthenticationInfo;

try
{
mailClient.Send(email);
}
catch (Exception ex)
{
return ex.ToString();
}

return “1”;
}

You ccan use any of the above methods, some mail server only supports authenticated email send by script. In that case, go for 2nd method.

Don’t forget to reference “using System.Net.Mail;” namespace for sending an email.

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SQL Server Truncate Transaction Log script

Many time, it happens in live sql server which has big transaction log file, you run DBCC SHRINKFILE command and see no effect of it on the transaction log file. You can use following magical five line at that time and you will done. It shrinks the trucated log File to minimum size possible.
————————————————————————-
USE DBName
GO
DBCC SHRINKFILE(transactionlogfilename, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(transactionlogfilename, 1)
————————————————————————
Happy Working!!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

COALESCE() function in SQL-Server for getting comma seperated value


Microsoft Definition:
This function returns first nonnull expresion among its argument.

You can use this function to get column values in one record set with separator like comma, dash etc. Before invention of this function, people used to achieve this kind of stuff by cursor which is time consuming. So, lets have a look at magical function by Microsoft.

Create following table in your database.
create table Testing( mid int ,name varchar(10) ,[18QI] varchar(10))

After creating the table, insert following records.
insert into Testing VALUES(1,’BC’,’01’)
insert into Testing VALUES(2,’BC’,’10’)
insert into Testing VALUES(3,’BC’,’02’)
insert into Testing VALUES(4,’BC’,’04’)

Now, we are all set to see the new powerful function COALESCE() in SQL-Server. Too see the magic, create following function in your database.

CREATE FUNCTION DBO.Get18QI(@ID char(100))RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @QIList varchar(1000)
SELECT @QIList = COALESCE(@QIList + ‘, ‘, ”) + convert(varchar,T.[18QI]) FROM Testing T WHERE T.name = @ID and T.[18QI] is not null
RETURN @QIList
END

after successfully creating the function, here we go to see the cool result by following query.
select DBO.Get18QI(‘BC’) as ComaValues

Hope you people will find this article useful.
Happy Working!!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah