Rank, Dense - Rank & Row - Number

Rank, Dense_Rank & Row_number
 Row_number : Returns an increasing unique number for each row starting at 1.

 Rank : it gives the same rank for duplicate values and skip the ranking.
 Dense_Rank : it gives the same rank for duplicate but it doesn’t skip the ranking.
Find Nth highest salary
Select Name, Salary, Gender, Row_Number() Over (Order by salary desc) as RowNumber,
Rank() Over(order by salary desc) as Rank,
Dense_Rank() Over(order by salary desc) as denseRank
From employees.
Delete Duplicate rows from table
With CTE As (
Select Name,Gender, salary row_number() Over (Partition by Name, gender, salary order by Name,
gender, salary ) row_num From Employee
Delete from CTE where row_num>1
Union and Union ALL
Union: combine two or more result set into a single set, without duplicate.
Union All: combine two or more result set into a single set, with duplicate.
Index
Indexes makes search operation faster.
 Table scan: SQL server search data sequentially one by one from the start to the last record of
the table.
 Index Scan/ Seek in SQL server: SQL server search the data by using B-Tree structure.
Clustered and Non-Clustered Index
Clustered Index: Clustered index defines the order in which data is physically stored in the table.
Non Clustered Index: Data is stored in one place and the index is stored in another place, and the index
will have the pointer of actual data.
Current Session and Current scope
Session is current database connection. The scope is the current query or current stored procedure.
View in SQL
View is a virtual table.
Difference between view and table.
 Table is physical whereas view is logical.

 A table is independent object whereas view is dependent on table.
Limitations:
We can’t pass parameters to SQL Server views.
View can’t be created based on temporary table.
We can’t use order by clause with views without specifying For XML, OFFSET, and TOP.
Trigger:
It is a special kind of stored procedure that automatically get executed before or after Insert, Update,
and Delete in a table.
There are two types of trigger:
1. Instead of trigger: If you want to add some logic instead of DML operation.
2. After Trigger: If you want to add some logic after DML operation.
E.g.
Create Trigger trUpdateEmployee

On Employee
For Update
As
Begin
Print ‘You can’t perform updated operation.’
Rollback transaction
End
DDL Trigger:
Create Trigger trRestrictAltertable
On Database
For Alter_Table
As
Begin
Print ‘You can’t Alter table.’
Rollback transaction
End
CTE
Common table expression is a temporary result set that reference with in select, Insert, Update, and
Delete.
With CTEExample as
Select ID from books.
Select * from CTEExample

Difference between join and union
Join Union
Combines the data based on matched Combines the result-set of two or more select
condition. statements.
It combines the data into new columns. It combines the data in a new row.
Number of column selected for each table can Number of columns selected for each table should be
be different. same.
Data type of selected columns can be different. Data type of selected columns should be same.
Temporary table and table variable
Ans. Some points regarding temporary table and table variable
 Table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in
the tempdb database. However, if there is a memory pressure the pages belonging to a table
variable may be pushed to tempdb.
 Table variables cannot be involved in transactions, logging or locking. This makes @table faster then
#temp. So table variable is faster then temporary table.
 Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE
INDEX instead they can have index by using Primary Key or Unique Constraint.
 Table variable can be passed as a parameter to functions and stored procedures while the same
cannot be done with Temporary tables.
 Temporary tables are visible in the created routine and also in the child routines. Whereas, Table
variables are only visible in the created routine.
 Temporary table allows Schema modifications unlike Table variables.
Important Points to remember:
 Temporary Tables are physically created in the tempdb database. These tables act as the normal
table and also can have constraints, index like normal tables.
 Table Variable acts like a variable and exists for a particular batch of query execution. It gets
dropped once it comes out of batch. It is created in the memory database but may be pushed out to
tempdb.
 Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.

Rank, Dense - Rank & Row - Number

Uploaded by

Copyright:

Available Formats

Rank, Dense - Rank & Row - Number

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Rank, Dense - Rank & Row - Number

Uploaded by

Copyright:

Available Formats

Rank, Dense_Rank & Row_number

 Row_number : Returns an increasing unique number for each row starting at 1.

Find Nth highest salary

Rank() Over(order by salary desc) as Rank,

Dense_Rank() Over(order by salary desc) as denseRank

Delete Duplicate rows from table

Delete from CTE where row_num>1

Union and Union ALL

Indexes makes search operation faster.

Clustered and Non-Clustered Index

Current Session and Current scope

View is a virtual table.

Difference between view and table.

 Table is physical whereas view is logical.

We can’t pass parameters to SQL Server views.

View can’t be created based on temporary table.

There are two types of trigger:

Create Trigger trUpdateEmployee

Create Trigger trRestrictAltertable

Print ‘You can’t Alter table.’

Select ID from books.

Select * from CTEExample

Temporary table and table variable

Ans. Some points regarding temporary table and table variable

Important Points to remember:

You might also like