Differnece Between RANK, DENSE_RANK and ROW_NUMBER

 /*Rank , RowNumber, Danse rank*/

Rank Function - It is used to get the rank of records on the basis of any column. It skips the records if matched the same.

DENSE_RANK Function - It is used to get the rank of records on the basis of any column. It doesn't skips the records if matched the same.

ROW_NUMBER Function - It is used to get the order of records on the basis of any column. It reset itself once the shorted column values gets changed.

CREATE TABLE Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
(
id INT,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL
)



INSERT INTO Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
VALUES
(1, 'Corrolla', 'Toyota', 1800),
(2, 'City', 'Honda', 1500),
(3, 'C200', 'Mercedez', 2000),
(4, 'Vitz', 'Toyota', 1300),
(5, 'Baleno', 'Suzuki', 1500),
(6, 'C500', 'Mercedez', 5000),
(7, '800', 'BMW', 8000),
(8, 'Mustang', 'Ford', 5000),
(9, '208', 'Peugeot', 5400),
(10, 'Prius', 'Toyota', 3200),
(11, 'Atlas', 'Volkswagen', 5000),
(12, '110', 'Bugatti', 8000),
(13, 'Landcruiser', 'Toyota', 3000),
(14, 'Civic', 'Honda', 1800),
(15, 'Accord', 'Honda', 2000)

RANK Function
The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause.

SELECT name,company, power,
RANK() OVER(ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number

Output
name        company        power    PowerRank
800            BMW             8000    1
110            Bugatti            8000    1
208            Peugeot          5400    3
Atlas        Volkswagen     5000    4
Mustang        Ford           5000    4
C500        Mercedez        5000    4
Prius        Toyota              3200    7
Landcruiser    Toyota       3000    8
Accord        Honda          2000    9
C200        Mercedez        2000    9
Corrolla    Toyota            1800    11
Civic        Honda             1800    11
City        Honda               1500    13
Baleno        Suzuki          1500    13
Vitz        Toyota               1300    15




SELECT name,company, power,
RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number

name                                               company                                            power       PowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                 8000        1
110                                                Bugatti                                               8000        1
Mustang                                            Ford                                               5000        1
Accord                                             Honda                                              2000        1
Civic                                              Honda                                                1800        2
City                                               Honda                                                 1500        3
C500                                               Mercedez                                          5000        1
C200                                               Mercedez                                          2000        2
208                                                Peugeot                                              5400        1
Baleno                                             Suzuki                                              1500        1
Prius                                              Toyota                                                3200        1
Landcruiser                                        Toyota                                           3000        2
Corrolla                                           Toyota                                              1800        3
Vitz                                               Toyota                                                 1300        4
Atlas                                              Volkswagen                                        5000        1




DENSE_RANK Function

The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records. Take a look at the following script.

SELECT name,company, power,
dense_RANK() OVER(ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number


name                                               company                                            power       PowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        1
208                                                Peugeot                                            5400        2
Atlas                                              Volkswagen                                         5000        3
Mustang                                            Ford                                               5000        3
C500                                               Mercedez                                           5000        3
Prius                                              Toyota                                             3200        4
Landcruiser                                        Toyota                                             3000        5
Accord                                             Honda                                              2000        6
C200                                               Mercedez                                           2000        6
Corrolla                                           Toyota                                             1800        7
Civic                                              Honda                                              1800        7
City                                               Honda                                              1500        8
Baleno                                             Suzuki                                             1500        8
Vitz                                               Toyota                                             1300        9


SELECT name,company, power,
DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS DensePowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number

name                                               company                                            power       DensePowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        1
Mustang                                            Ford                                               5000        1
Accord                                             Honda                                              2000        1
Civic                                              Honda                                              1800        2
City                                               Honda                                              1500        3
C500                                               Mercedez                                           5000        1
C200                                               Mercedez                                           2000        2
208                                                Peugeot                                            5400        1
Baleno                                             Suzuki                                             1500        1
Prius                                              Toyota                                             3200        1
Landcruiser                                        Toyota                                             3000        2
Corrolla                                           Toyota                                             1800        3
Vitz                                               Toyota                                             1300        4
Atlas                                              Volkswagen                                         5000        1




ROW_NUMBER Function


Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function simply returns the row number of the sorted records starting with 1

SELECT name,company, power,
ROW_NUMBER() OVER(ORDER BY power DESC) AS RowRank
FROM Cars

name                                               company                                            power       RowRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        2
208                                                Peugeot                                            5400        3
Atlas                                              Volkswagen                                         5000        4
Mustang                                            Ford                                               5000        5
C500                                               Mercedez                                           5000        6
Prius                                              Toyota                                             3200        7
Landcruiser                                        Toyota                                             3000        8
Accord                                             Honda                                              2000        9
C200                                               Mercedez                                           2000        10
Corrolla                                           Toyota                                             1800        11
Civic                                              Honda                                              1800        12
City                                               Honda                                              1500        13
Baleno                                             Suzuki                                             1500        14
Vitz                                               Toyota                                             1300        15

SELECT name, company, power,
ROW_NUMBER() OVER(PARTITION BY company ORDER BY power DESC) AS RowRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number


name                                               company                                            power       RowRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        1
Mustang                                            Ford                                               5000        1
Accord                                             Honda                                              2000        1
Civic                                              Honda                                              1800        2
City                                               Honda                                              1500        3
C500                                               Mercedez                                           5000        1
C200                                               Mercedez                                           2000        2
208                                                Peugeot                                            5400        1
Baleno                                             Suzuki                                             1500        1
Prius                                              Toyota                                             3200        1
Landcruiser                                        Toyota                                             3000        2
Corrolla                                           Toyota                                             1800        3
Vitz                                               Toyota                                             1300        4
Atlas                                              Volkswagen                                         5000        1










Similarities between RANK, DENSE_RANK, and ROW_NUMBER Functions
The RANK, DENSE_RANK and ROW_NUMBER Functions have the following similarities:
1- All of them require an order by clause.
2- All of them return an increasing integer with a base value of 1.
3- When combined with a PARTITION BY clause, all of these functions reset the returned integer value to 1 as we have seen.
4- If there are no duplicated values in the column used by the ORDER BY clause, these functions return the same output.

Azure SQL Server Removinng the duplicated value without rank function

 

Select SQLWorkorderType,sum(sqlworkordertypeid)

 from(

select distinct SQLType as [SQLWorkorderType],0 as sqlworkordertypeid 

from SQLWorkOrderType WOT
Union
select distinct SqlType as [SqlWorkorderType],sqlworkordertypeid from SQLWorkOrderType WOT
) as B GROUP by SqlWorkorderType

Azure SQL Server Date and Time DataType Conversions and Functions

 This document will explain the types of Date and Time  Data Type in Sql Server and related funtions.

In SQL Server Their are six Date and Time Data Types.

1- Time

2- Date

3-SmallDateTime

4-DateTime

5-DateTime2

6-DateTimeOffSet

Time 

The fractional second scale specifies the number of digits for the fractional part of the seconds. The fractional second scale ranges from 0 to 7. By default, the fractional second scale is 7 if you don’t explicitly specify it.

Format-        hh:mm:ss[.nnnnnnn]

Range-        nano seconds upto 100

Query- To create a table  with Time Column

CREATE TABLE dizsweb(
    dizswebid int

    Name varchar(50)    

    start_at TIME(0),

);

 

 The following INSERT statement adds a row to the table

INSERT INTO dizsweb (
    dizswebid ,
    Name ,
    start_at 
)
VALUES
    (   '1'
        'John Doe',
        '09:30:00'
    );

 

 

Date
To store the date data in the database, you use the SQL Server DATE data type

Format-        YYYY-MM-DD

Range-        1 Day

Query to Create a Table with Date Data Type

Create Table Dizsweb (DizswebID int, Name varchar(50), DOB Date)

Insert the data into table

Insert into Dizsweb values( 1,'SQL Azure','1994-07-28')

 

 

CREATE TABLE dizsweb (
    product_id INT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    amount DEC (10, 2) NOT NULL,
    PRIMARY KEY (
        product_id,
        valid_from,
        valid_to
    ),
    FOREIGN KEY (product_id) 
    REFERENCES production.products (product_id)
);

 

INSERT INTO dizsweb (
    product_id,
    valid_from,
    valid_to,
    amount
)
VALUES
    (
        1,
        '2019-01-01',
        '2019-12-31',
        400
    );

 

SmallDateTime

Format-        YYYY-MM-DD hh:mm:ss

Range-        1 Minute

CREATE TABLE [dbo].[delivers](

  [productid] [tinyint] NOT NULL,

  [date] [nvarchar](100) NULL,

CONSTRAINT [PK_delivers] PRIMARY KEY CLUSTERED

(

  [productid] 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

INSERT [dbo].[delivers] ([productid], [date]) VALUES (1, N'02-03-2005')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (2, N'03-05-2006')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (3, N'04-05-2011')

 

DateTime

Format-        YYYY-MM-DD hh:mm:ss[.nnn]

Range-        0.33 Seconds

 

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);

 

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25) default 'United States',
Birth_Date datetime); 

DateTime2

Format-        YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Range-        100 nano Seconds

    DECLARE @nowDateTime 

    DATETIME = GETDATE(),

    DateTime2 DATETIME2(3)= SYSDATETIME() 

    SELECT DATALENGTH(DateTime) 'DateTime Storage Size', 

    DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'

DateTimeOffSet

Format-        YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Range-        100 nano seconds and as par the time Zone

 

 

CREATE TABLE dizsweb (

    dizsweb DATETIMEOFFSET(7)

);

INSERT INTO dizsweb (dizsweb)
VALUES(
       CAST('2019-02-28 01:45:00.0000000 -08:00' AS DATETIMEOFFSET)); 

 

 

 

SQL server Upgrade

 

When you upgrade to a new version of SQL Server, there are some critical things you should do to help avoid any surprise SQL Server performance issues. I mean new version  of SQL Server is 2016,2017 or 2019, Because Microsoft  closed its support up to some extent to older version

Why need the Requirement to upgrade the Version?

Basically it is because Microsoft ended Its support of some versions.

There are two type of Support

1- MainStream Support

2- Extended Support

SQL Server Versions	MainStream Support	Extended Support
SQL Server 2005	April 12, 2011	April 12, 2016
SQL Server 2008 or R2	July 8, 2014	July 9,2019
SQL Server 2012	July 11, 2017	July 12, 2022
SQL Server 2014	July 9, 2019	July 9, 2024
SQL Server 2016	July 13, 2021	July 14, 2026
SQL Server 2017	Oct 11, 2022	Oct 12, 2027
SQL Server 2019	July 1, 2025	Aug 1, 2030

For Upgrading any Version there are some preparations which need to be taken care.

1- Find the Upgrade Blockers. 

2- Clear Understanding of Version and Editions.

3- Clear Understanding of Features introduced and obsolete

3- Make a List of objects which use the features which  are obsolete from the newer version and currently in use.

4- Clear Hardware Requirement for newer Versions. 

For this Activity i am upgrading the Server from 2012 to 2017.

Process to Upgrade the SQL Server Version.

There are 3 ways to upgrade

1. - In-Place Upgrade

2. - Side-by-Side Upgrade

3. - Rolling Upgrade

Steps for an In-Place Upgrade 

In-place upgrades are the easiest to perform, but the most difficult to rollback should there be any issues. The steps involved in an in-place upgrade are as follows:

1. Verify that backups exist for all databases (user and system). 

2. Review the list of requirements for SQL server 2017 and install whatever is needed.

3. Install SQL Server 2017 

4. Restore the Backup

Side-by-Side Upgrade

• 1- Backup all the database

• 2-Script out any and all necessary system objects.

            3-Review the list of requirements for SQL server 2017 and install whatever is needed.

• 4- Run the script to create the Object

• 5-Select database(s) to migrate and it take offline. 

• 6-Migrate database to new instance. Repeat for each database.

•  

 

 

 

Rolling Upgrade

1- Select any High-Availability 

2- Install new Server instance 2017 and make it  as a Secondary node

3- Fail the Primary Node now New Instance will be active and will be Primary Node

 

Checklist need to Perform Before Planning to Upgrade about version of Server

1-Checking of Deprecated Features  in new version

SELECT * FROM sys.dm_os_performance_counters   WHERE object_name  = 'MSSQL$JFT:Deprecated Features   

2- Get the List top indexes which are in use maximum times in the current version.

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name

      ,IX.name                     AS Index_Name

      ,IX.type_desc                 AS Index_Type

      ,SUM(PS.[used_page_count]) * 8     IndexSizeKB

      ,IXUS.user_seeks AS             NumOfSeeks

      ,IXUS.user_scans AS             NumOfScans

      ,IXUS.user_lookups AS             NumOfLookups

      ,IXUS.user_updates AS             NumOfUpdates

      ,IXUS.last_user_seek AS         LastSeek

      ,IXUS.last_user_scan AS         LastScan

      ,IXUS.last_user_lookup AS         LastLookup

      ,IXUS.last_user_update AS         LastUpdate

FROM sys.indexes IX

INNER JOIN sys.dm_db_index_usage_stats IXUS 

ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats PS 

on PS.object_id=IX.object_id

WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1

GROUP BY 

OBJECT_NAME(IX.OBJECT_ID) ,

IX.name ,

IX.type_desc ,

IXUS.user_seeks ,

IXUS.user_scans ,

IXUS.user_lookups,

IXUS.user_updates ,

IXUS.last_user_seek ,

IXUS.last_user_scan ,

IXUS.last_user_lookup ,

IXUS.last_user_update

3- Current Used Space at current instance and Available Space in the new instance.

4- Current OS Hardware Configuration.

5- Scripting of all user defined Objects.