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

Row_Number

 

 

 

 

 

 

 

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

Row_numberPartitionby

 

 

 

 

 

 

 

 

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

rank

 

 

 

 

 

 

 

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

Dense-rank

 

 

 

 

 

 

 

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.

Untitled

Schedule Job Related Information from Sqlserver

Today I am sharing for, how we get list of jobs schedule in Microsoft Sqlserver?
Use below query to get all information about jobs schedule in Microsoft Sqlserver.

use msdb
select distinct a.name JOBNAME, isnull(DESCRIPTION,’No description available’) DESCRIPTION,c.Name Category,
isnull (b.database_name,’None’) DATABASE_NAME, isnull(f.name, ‘None’) Job_Owner,
case when d.name is null then ‘No Schedule’ else d.name end Schedule,
isnull (case d.freq_type
when ’1 ‘ then ‘Once’
when ’4′ then ‘Daily’
when ’8′ then ‘Weekly’
when ’16′ then ‘Monthly’
when ’32′ then ‘Monthly relative’
when ’64′ then ‘When SQL Server Agent starts’ end, ‘None’) as Frequency,
isnull (case d.freq_interval
when ’1′ then ‘None’
when ’2′ then ‘Monday’
when ’4′ then ‘Tuesday’
when ’8′ then ‘Wednesday’
when ’16′ then ‘Thursday’
when ’32′ then ‘Friday’
when ’64′ then ‘Saturday’
end,’None’) as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date
from sysjobs a
Inner join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
where a.enabled = 1
order by a.name

Hope this post will useful for you.

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)

Example

 

 

 

 

 

 

 

 

 

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.

Database Design Tips for Microsoft Sqlserver

Today I will explain how we make decision on which Type we need to use for what purpose at the time of database design.
It’s very small article but useful for database design.

    • Char or Varchar ?
  • When you know that I need to give fix input in the field.
    Example like below

      ‘Y’ or ‘N’
      ‘Yes’ or ‘No’
      ‘Activate’ or ‘Deactivate’ etc

    Then use char type over varchar.

    CHAR can be faster and more space efficient.
    Microsoft SQL has 2 bytes of overhead for a VARCHAR, to get length of the string input in Field.

      Varchar or nVarchar ?

    When we need to store UNICODE character or provide multilingual support then use nvarchar as datatype.

      Datetime vs Smalldatetime

    Date time – January 1, 1753 through December 31, 9999 takes 8 bytes.
    Small date time – January 1, 1900, through June 6, 2079 takes 4 bytes.

    I hope this is useful for designing database.

    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.