Differences between Row_Number(), Rank(), Dense_Rank() Functions in SQL Server

All three function  known as a window function in Microsoft SQL Server, the difference between rank(), dense_rank(), and row_number() comes when you have duplicate records in a columns where you want to apply ranking For example, if you are ranking employees by their salaries then what would be the rank of two employees of the same salaries? It depends on which ranking function you are using like row_number, rank, or dense_rank.

CREATE TABLE [dbo].[employee](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [name] [nvarchar](50) NULL,
       [age] [int] NULL,
       [salary] [decimal](18, 0) NULL
)

SELECT *
FROM employee

 

 

ROW_NUMBER():- The row_number() function always generates a unique ranking even with duplicate records. As you can see below in  query result.

SELECT *,
       ROW_NUMBER() OVER (ORDER BY id) AS Ranking
FROM employee

 

RANK():-  The rank() function will assign the same rank to the same values . But, the next different rank will not start from immediately next number but there will be a double increment like u can see in employee id 1 that is start from ranking 3 skip the ranking 2.

SELECT *,
       RANK() OVER (ORDER BY salary ) AS Ranking
FROM employee

 

 

 

 

Dense_Rank():-  The dense_rank function is similar to the rank() window function i.e. same values will be assigned the same rank, but the next different value will have a rank which is just one more than the previous rank.

 

SELECT *,
       DENSE_RANK() OVER (ORDER BY salary ) AS Ranking
FROM employee

 

Differences between CAST() and CONVERT() Functions

 

# CAST() and CONVERT() Functions

The CAST and CONVERT functions are both used to convert data from one data type to another.

SELECT CAST ('10' as int) * 20;        --outpout=200

SELECT CONVERT (int, '10') * 20;       --outpout=200 

In both cases, we’re converting from the text value ’10’ to its integer representation. 

• In many ways CAST and CONVERT are similar. Both are used to convert data from one type to another.  Thought their syntax is different, both functions are able to convert values from one formation to another.

• CAST is part of the ANSI-SQL specification; whereas, CONVERT is not.  In fact, CONVERT is SQL implementation-specific.

Difference between Scope_Identity(), @@Identity, and Ident_Current(),Identity().

 

# SCOPE_IDENTITY(), IDENT_CURRENT, and @@IDENTITY

SCOPE_IDENTITY(), IDENT_CURRENT, and @@IDENTITY are similar functions because they return last values that are inserted into identity columns.

• SCOPE_IDENTITY(),  and  @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

• IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the last value generated for a specific table in any session and any scope. 

1. SELECT SCOPE_IDENTITY() AS SCOPEIDENTITYOUTPUT 
2. SELECT IDENT_CURRENT( 'table_or_view' ) AS IDENTOUTPUT 
3. Select @@IDENTITY AS IDENTITYOUTPUT  

Note:- The session is the database connection. The scope is the current query or the current stored procedure.

Identity(1,1):- Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally cannot insert a value into an identity column. Identity column can be used to uniquely identify the rows in the table. 

Syntax:- IDENTITY [( seed, increment)] 

Seed: Starting value of a column, default value is 1. 

Increment: Incremental value that is added to the identity value of the previous row that was loaded. The default value 1.

Over and PartitionBy Clause in Sql Server.

 

# Over and PartitionBy Clause 

• The OVER clause in SQL Server is used with PARTITION BY to break up the data into partitions. 

• The PARTITION BY clause is a subclause of the OVER clause. The PARTITION BY clause divides a query’s result set into partitions.In other words, PARTITION BY defines the groups into which the rows are divided.The PARTITION BY clause is basically used to partition the result set into multiple groups. 

Differences Between IsNull() and Coalesce() Functions in SQL Server

# Differences Between IsNull() and Coalesce() Functions in SQL Server 

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.The SQL Coalesce and IsNull functions are used to replace null value with another value of a column. 

1.The ISNULL() function is a Transact-SQL function whereas COALESCE() function is based on the ANSI SQL standard.  

2.The ISNULL() function contains only two parameters. The COALESCE() function contains multiple parameters.

ISNULL

Syntax:- ISNULL(expression,replacement_value)
example:- ISNULL(mobile_number, 'N/A'); output:- N/A

COALESCE

syntax:- COALESCE(val1, val2, ...., val_n)
example:- COALESCE(NULL, 1, 2, 'Google.com'); output:- 1

3.The ISNULL() function contains various types of parameters. The COALESCE() function doesn't limit the number of arguments, but they must all be of the same data type.

4.The ISNULL() function looks at the first value and the second parameter value is automatically limited to that length but COALESCE() does not have this restriction.

Example:- Create a table student and insert value in it.

create table student

(

    id int identity,

    rollnumber int,

    name nvarchar(50) not null,

    mobile_number nvarchar(10),

    city nvarchar(50) not null,

    hobby nvarchar(50)

)

 

insert into student
select 101, 'John','235546525','New York','Tennis'
union all
select 102, 'Rita',null,'Texas',null
union all
select null, 'Avira',null,'Missicippi','Shotting'
union all
select 101, 'Smith','5522551235','Washington-DC',null

      Output

           select * from student

   Now replace the NULL with some user friendly data using ISNULL function .

select ISNULL(rollnumber, 0) as rollnumber,

       name,

       ISNULL(mobile_number, 'N/A') as mobile_number,

       City,

       ISNULL(hobby, 'Unknown') as hobby

from student

        

              Now replace the NULL with some user friendly data using COALESCE function .

select COALESCE(rollnumber, 0) as rollnumber,

       name,

       COALESCE(mobile_number, 'N/A') as mobile_number,

       City,

       COALESCE(hobby, 'Unknown') as hobby

from student

                     

       

Difference between Identity & Sequence in SQL Server.

 

# Difference between Identity & Sequence in SQL Server.

• IDENTITY is a table column property.The IDENTITY property is tied to a particular table and cannot be shared among multiple tables since it is a table column property.

• On the flip side the SEQUENCE is a database object defined by the user and can be shared by multiple tables since is it is not tied to any table.

• To generate the next IDENTITY value, a new row has to be inserted into the table. On the other hand, the next VALUE for a SEQUENCE object can simply be generated using the NEXT VALUE FOR clause with the sequence object.

• The value for the IDENTITY property cannot be reset to its initial value(Except delete table with truncate). In contrast, the value for the SEQUENCE object can be reset.

• A maximum value cannot be set for the IDENTITY property. On the other hand, the maximum value for a SEQUENCE object can be defined.