Ranking Functions in Microsoft SQLSERVER

Today, We will see example of different ranking functions supported in Sqlserver.

Use below script to create table.

CREATE TABLE dbo.CustomerData
(OrderId INT IDENTITY(1,1) NOT NULL,CustomerId INT,TotalAmt decimal (10,2))

Populate data in table.

INSERT dbo.CustomerData (CustomerId, TotalAmt)
SELECT 1, 100
UNION ALL
SELECT 1, 2000
UNION ALL
SELECT 1, 560
UNION ALL
SELECT 1, 1000
UNION ALL
SELECT 1, 1000
UNION ALL
SELECT 2, 1000
UNION ALL
SELECT 2, 2500
UNION ALL
SELECT 2, 500
UNION ALL
SELECT 2, 1500
UNION ALL
SELECT 3, 1500

Below are list of Ranking function.

1. ROW_NUMBER – It will generate Row Number base on order by and partition by  provided in over clause.

SELECT *,ROW_NUMBER() OVER (ORDER BY TotalAmt DESC) AS ROWNUMBER
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

 

 

 

 

 

 

 

SELECT *,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS ROWNUMBERPartitionby
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

 

 

 

 

 

 

 

 

2. RANK – It will Generate rank base on order by and partition by provided in over clause.

Partition by – What ever column written in partition by SQL will logically create partition on that and after on that partition Rank function will apply. Rank will give same rank if value is same for column provided in over clause in order by.

SELECT *,RANK() OVER (ORDER BY TotalAmt DESC) AS RANKF,
RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS RANKFPartitionby
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

 

 

 

 

 

 

 

3. DENSE_RANK – It will Generate rank base on order by and partition by provided in over clause.

Partition by – What ever column written in partition by SQL will logically create partition on that and after on that partition DENSE_Rank function will apply. DENSE_Rank will give same rank if value is same for column provided in over clause in order by, and next number in sequence will generate base on last number use in sequence, you can see in below example.

SELECT *,DENSE_RANK() OVER (ORDER BY TotalAmt DESC) AS DENSERANK,
DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS DENSERANKPartitionby
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

 

 

 

 

 

 

 

Below is query by which you compare all the ranking function.

SELECT *,

ROW_NUMBER() OVER (ORDER BY TotalAmt DESC) AS ROWNUMBER,

ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS ROWNUMBERPartitionby,

RANK() OVER (ORDER BY TotalAmt DESC) AS RANKF,

RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS RANKFPartitionby,

DENSE_RANK() OVER (ORDER BY TotalAmt DESC) AS DENSERANK,

DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS DENSERANKPartitionby

FROM dbo.CustomerData

ORDER BY TotalAmt DESC

For partition by comparison see customerid and base on that see all rank function output.

How to Remove duplicate rows from result ?

Today I explain how to remove duplicate value from result.
Below I am creating one table and I am putting only one column, but this scenario will work for more than one columns also.
— Create table
create table t1(id int)
— Insert data
insert into t1
values(1)
insert into t1
values(1)
insert into t1
values(2)
insert into t1
values(2)
insert into t1
values(3)
insert into t1
values(3)
insert into t1
values(4)
insert into t1
values(4)
— Use Cte for getting data
;with cte
as
(select ROW_NUMBER() OVER(Partition by id order by id) as idno,id from t1)
select * from cte where idno = 1

I hope this post will be useful for you.

Simplest way to get data from Coma separated String

There are many ways you will get value from coma or any other separator string, but today I will show you simplest approach to get value from string by using XML.


DECLARE @String varchar(max),
@Splitchar char(1),
@Xmlvalue xml

set @String = ‘10,20,30,40’
set @Splitchar = ‘,’

SELECT @Xmlvalue = CONVERT(xml,’ <root> <Node> ‘ + REPLACE(@String,@Splitchar,'</Node> <Node>’) + ‘ </Node> </root>’)
SELECT [Value] = T.c.value(‘.’,’varchar(20)’)
FROM @Xmlvalue.nodes(‘/root/Node’) T(c)

 

 

 

 

 

 

 

 

 

I personally believe that , that’s simplest approach to get value from string which is coma or any other separated.

If you know any clear and good approach please give feed back so I can share it.

Add and Remove Link server in Microsoft Sqlserver

Sometime we need to query to database which are on other server, so for that we need to use Microsoft Sqlserver feature Linkserver.

Add link Server

EXEC sp_addlinkedserver
@server = 'SVT-SRV-SQL',
@srvproduct = 'SQLServer',
@provider = 'SQLNCLI', -- provider is depends on your version of link server that you need to add
@datasrc = 'SVT-SRV-SQL'

Login to link server

exec sp_addlinkedsrvlogin 'SVT-SRV-SQL', 'false', NULL, 'Test', 'Test';

Get the list of link server to check your server is added or not.

exec sp_linkedservers

Check by accessing link server

select * from [Svt-srv-sql].Databasename.dbo.Tablename

Remove Link server

exec sp_dropserver 'SVT-SRV-SQl','droplogins'


For more information please check on LinkServer.

Learn Basic LINQ by Comparision of Sqlserver Query with C# LINQ

Today I will explain simple LINQ query base on Microsoft sqlserver query, so any sqlserver developer can able to learn LINQ.
Create table like below


/****** Object: Table [dbo].[Student] Script Date: 7/11/2013 4:31:48 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Student](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[PhoneNo] [varchar](50) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Run below script to insert rows in table student.


Insert into Student
values(1,'John','9090909091')
go
Insert into Student
values(2,'Jack','9090909092')
go
Insert into Student
values(3,'Bob','9090909093')
go
Insert into Student
values(4,'Denis','9090909094')
go
Insert into Student
values(5,'Bush','9090909095')
go


It’s done for the Sqlserver side, now create class like below in any application
Either windows application or web application or console application in C#.


public class Student
{
int varid;
string varname = string.Empty;
string varphoneNo = string.Empty;

public Student(int pid,string pname,string pphoneNo)
{
Id = pid;
Name = pname;
Phoneno = pphoneNo;
}

public int Id { get { return varid; } set { varid = value; } }
public String Name { get { return varname; } set { varname = value; } }
public String Phoneno { get { return varphoneNo; } set { varphoneNo = value;
}

Use below function to fill value in List.


public List FillList()
{
List studentlist = new List();
Student student1 = new Student(1, "John", "9090909091");
Student student2 = new Student(2, "Jack", "9090909092");
Student student3 = new Student(3, "Bob", "9090909093");
Student student4 = new Student(4, "Denis", "9090909094");
Student student5 = new Student(5, "Bush", "9090909095");
studentlist.Add(student1);
studentlist.Add(student2);
studentlist.Add(student3);
studentlist.Add(student4);
studentlist.Add(student5);
return studentlist;
}


Then call this function and fill value in this, put this in any of the code where you need to run.


List listofStudent = FillList();


Below is table which defines query syntax comparison for sqlserver and LINQ.

Feature	Sqlserver	LINQ
Simple Select Query	Select *  from Student	var Queryresult = from result in test select result;
Where Condition operator – Number column compare	Select * from Student where id = 1	var Queryresult1 = from result in listofStudent Where result.Id == 1 select result;
String compare	select * from student where Name = ‘John’	var Queryresult2 = from result in listofStudent where result.Name == “John” select result;
Like clause	Select * from student where Name like ‘Jo%’	var Queryresult3 = from result in listofStudent where result.Name.StartsWith(“J”) select result;
Order by clause	Select * from student where Name like ‘J%’ order by id	var Queryresult4 = from result in listofStudent where result.Name.StartsWith(“J”) orderby result.Id select result;

 

 

So, that’s basic comparison of the Sqlserver and LINQ query, I hope this is helpful for you to basic understanding of the LINQ base on sqlserver.

Sqlserver Query optimization Tips

Hello friends, I have learn some useful tips that i fill i need to put on blog so i am sharing with you, so below is the list of Query optimization Tips.

1. Restrict the Query result. Means return the required row and column from the query, This helps in reducing network traffic.

2. User Store procedure in place of the Queries. Store procedure is compile object so next time i will use execution plan created by the sqlserver engine.

3. Avoid Cursor that will improve your performance. Cursor fetches records row by row so it will make round trips.

4. Avoid Trigger if not needed. It will execute every time when data gets updated.

5. Use Table variable in place of the Temporary table. Temporary table will create in Tempdb it’s overhead to create table. Table variable is doesn’t need to create in the memory and it’s automatically remove when transaction complete.

6. Use “where” in place of Having clause.

7. Use “SET NOCOUNT ON” in store procedure, It’s stops sending message of rowcount and reduce traffic.

8.Use Dynamic Query with this syntax.Check below link
https://sqlmca.wordpress.com/2010/11/10/use-of-dynamic-sqlquery-and-its-optimization/

9. Use indexing where you need.(use it carefully)

10. Update your statistics in regular interval. Update Statistics will rebuild your indexes and give your good performance.

11. Check your execution plan for the No table scan.

I hope that will be useful tips for you and you can use it practically.
If you like it or any query give your valuable time by writing comments.

USE of XML datatype, Read XML and Write XML in Sqlserver

ML data type is very useful in SQLserver 2005 and 2008.

Check the below script and learn how to use XML data type.

Create Table Patient(FirstName Varchar(20),LastName Varchar(20))
insert into Patient
values(‘Jack’,’Sparrow’)
insert into Patient
values(‘Peter’,’Parker’)
insert into Patient
values(‘John’,’Black’)

Below is the simple Feature of the XML.
You can set your table data in the XML type of variables
And read that.
Important feature is you can pass that XML type variables
To any procedure and you can read and process that data.
It’s like you pass table from one procedure to another procedure.

DECLARE @xmlDoc XML
SET @xmlDoc =
(
SELECT FirstName,LastName FROM Patient
FOR XML AUTO
)
SELECT @xmlDoc

Below code will fetch data from xml variable created above.

select
SelCat.CatDet.value(‘@FirstName’, ‘varchar(20)’) as FirstName,
SelCat.CatDet.value(‘@LastName ‘, ‘varchar(20)’) as LastName
from
@xmlDoc.nodes(‘Patient’) as SelCat(CatDet)

Below is the example of Write the rows to the XML TYPE
And you can read that as rows in any procedure.
This is useful when you have to pass block of rows
Without defining the table name.
Below is the example of that.

DECLARE @xmlDoc XML
SET @xmlDoc =
(
SELECT FirstName,LastName FROM Patient
FOR XML RAW
)

Below code will fetch data from xml variable created above.

SELECT
SelXML.xmlDetail.value(‘@FirstName’, ‘Varchar(20)’) AS FirstName,
SelXML.xmlDetail.value(‘@LastName’, ‘varchar(20)’) AS LastName
FROM
@xmlDoc.nodes(‘row’) AS SelXML(xmlDetail)
drop table Patient

I hope this post is useful for you and upgrade your knowledge.

Use of CASE … WHEN in Where condition and optional parameter in SQL Procedure

I think every body know how to use the CASE in the select query.
Today i will explain use of CASE in the where condition with some specific logic triks.
Example :
DECLARE @Recordstatus AS BIT
SET @Recordstatus = 1
SELECT * FROM dbo.ADT_Columns
WHERE RecordStatus = (CASE @Recordstatus WHEN @Recordstatus THEN 0 ELSE 1 END)

if CASE clause returns some value then you use CASE in Where condition.

Another thing i will explain is logical use of case which remove the use of If condition and also use the optional parameter in procedure.
I think many developers know that feature but i think nobody uses this option  in sqlserver for the procedure level we use this at .net or clientside mostly but it is very useful feature. When you use one procedure at many places then you need not to pass this parameter at every place.
In below example i will cover both the topics.
— Run below code in AdventureWorks DB
— GetAddress ‘All’
— Procedure with IF condition
CREATE PROCEDURE GetAddress @city VARCHAR(50)
AS
BEGIN
IF @city = ‘All’
BEGIN
— Returns all Records whithout any condition
SELECT * FROM Person.Address
END
ELSE
BEGIN
— Returns all Records which match the condition
SELECT * FROM Person.Address
WHERE City LIKE @City
END
END
— GetAddressWihtCase ‘London’
— GetAddressWihtCase  ‘All’ or GetAddressWihtCase  ‘All’
— Procedure without IF condition and Optional Parameters
CREATE PROCEDURE GetAddressWihtCase @city VARCHAR(50) = ‘All’
— OPtional parameter
AS
BEGIN
SELECT * FROM Person.Address
WHERE City LIKE (CASE @city WHEN ‘All’ THEN City ELSE @city END)
— Use it’s own value in the condition this is very useful thing and independent to
— Technology you can use in Oracle or in MySQl too.

END

I hope this article is useful to enhance your knowledge about Sqlserver.If you like this article
then please give comment on this article.

Table value parameter new feature in SQLSERVER 2008.

Today i will explain very  nice and useful feature of the Sqlserver 2008.In the past i was facing many problem in reading and writing data in the Database from .Net environment for bulk insert and delete.I personally use XML to read the data from vb.net for implement bulk insert and delete.
After understanding this feature this thing is very easly done in future.
Below is the example of,
how we can pass Table as parameter to Procedure?

— Step 1
— Create table Student

CREATE TABLE [dbo].[Student](
[Student_ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](80) NOT NULL,
[City] [varchar](100) NOT NULL
) ON [PRIMARY]

GO

Step 2

— Insert the Rows in the Student Table
Insert into Student
values(‘Jhon’,’NewYork’)
Insert into Student
values(‘Mac’,’Paris’)
Insert into Student
values(‘Rahul’,’Delhi’)
Insert into Student
values(‘Jay’,’Mumbai’)
Insert into Student
values(‘Jack’,’London’)

select * from Student

Step 3
— very important
— Create the StudentTabelType as userdefine datatype of Table
CREATE TYPE [StudentTableType] AS TABLE
(
[Name] [varchar](80) NOT NULL,
[City] [varchar](100) NOT NULL
)
GO

— Create the Procedure which get the Table as input parameter
Create PROCEDURE StudentDetail(@StudentTable StudentTableType READONLY)
AS
BEGIN
INSERT INTO Student
SELECT * FROM @StudentTable

— You can create Tabel variable like this
–DECLARE @StudentTab StudentTableType
–insert into @StudentTab
–values (‘Rajesh’,’Bhopal’)
–insert into Student
–select * from @Studenttab
END
GO
You must pass Table variable as read only you can not prform
any DML command on this pass Table.
Like that you can pass table in the procedure and use for the further manipulation.

Now, one of the important thing is how to pass from .net environment.

Declar the datatable with two columns name and city.
Declare DTStudent as new datatable
DTstudent.Columns.add(“Name”)
DTstudent.Columns.add(“City”)

Insert some Records in this DTStudent Table.
Create Connection and command object and set all it’s property to establish connection to sqlserver.

Add parameter to command
Dim sqlcommad as new SqlClient.SqlCommand
Use the Structured datatype for the parameter and set the datatable to the
value of the parameter.

sqlcmd.Parameters.Add(“StudentTable”, SqlDbType.Structured).value = StudentTable

This feature is very useful in bulk insert and delete of data.

I hope this article is useful to you,give me feed back on this article.

How to get Start Date and End Date of Week from Given Date?

Today I will explain how you get the Start Date and End Date of week from any date.One of my colleague has requirement something like that he has week no and he want the start date and End date of that week.

There are many ways to find that solution but I got one solution that is useful to you if you want something like that in future.

Below is the Query from that you can get the start date and end date of the week.In this Query Replace order date with any date and you get the results from this query.

SELECT

CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST – DATEPART(dw, orderdate) – 6, orderdate)), 101) startdate

CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST – DATEPART(dw, orderdate), OrderDate)), 101) enddate,

from tbl_dietorder_trn (Any Table )

You can use Getdate() function and check this.

In this I use the @@DATEFIRST Function. By using this function you can set the first day of week. Sunday is default first day of week so it’s default value is 7.

This Query to find start date and End date id different from other because of @@DATEFIRST function.

You can set that function value between 1 to 7.Base on that thing this query is give you the result.

1 –  Monday
2 –  Tuesday
3 –  Wednesday
4 –  Thursday
5 –  Friday
6 –  Saturday
7 –  Sunday

You can set the value of @@DATEFIRST.

Set Datefirst 1

You can get this information from below link about @@DATEFIRST Function.

http://msdn.microsoft.com/en-us/library/ms187766.aspx

my question to you,
can any one know other than this Query that give First and Last Date of week?
I hope this is useful to you in future.Please give your feedback.