Ranking Functions – ROW_NUMBER,RANK,DENSE_RANK,NTILE

SQL Server 2005 introduced four new ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions allow you to analyze data and provide ranking values to result rows of a query. For example, you might use these ranking functions for assigning sequential integer row IDs to result rows or for presentation, paging, or scoring purposes.

All four ranking functions follow a similar syntax pattern:

function_name() OVER([PARTITION BY partition_by_list] ORDER BY order_by_list)

The basic syntax follows.


ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)
RANK() OVER ([<partition_by_clause>] <order_by_clause>)
DENSE_RANK() OVER([<partition_by_clause>]<order_by_clause>)
NTILE(integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)


Ranking functions are a subset of the built in functions in SQL Server. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional.

 

ROW_NUMBER: ROW-NUMBER function returns a sequential value for every row in the results. It will assign value 1 for the first row and increase the number of the subsequent rows.

 

Syntax:

SELECT ROW_NUMBER() OVER (ORDER BY column-name), columns FROM table-name

 

OVER – Specify the order of the rows

ORDER BY – Provide sort order for the records


RANK: The RANK function returns the rank based on the sort order. When two rows have the same order value, it provide same rank for the two rows and also the ranking gets incremented after the same order by clause.

Syntax:
SELECT
RANK() OVER ([< partition_by_clause >] < order_by_clause >)

SELECT RANK() OVER ([< partition_by_clause >] < order_by_clause >)

Partition_by_clause – Set of results grouped into partition in which RANK function applied.
Order_by_clause – Set of results order the within the partition


In the above example, based on the sort order Employee Name, the Rank is given.
The first two rows in the list has same Employee Name, those rows are given same Rank, followed by the rank of next for another set of rows because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.


DENSE_RANK: The DENSE_RANK function is very similar to RANK and return rank without any gaps. This function sequentially ranks for each unique order by clause.

Syntax:
SELECT DENSE_RANK() OVER ([< partition_by_clause >] <order_by_clause>)</order_by_clause>

SELECT DENSE_RANK() OVER ([< partition_by_clause >] )

Partition_by_clause – Set of reults grouped into partition in which DENSE RANK function applied.
Order_by_clause – Set of results Order the within the partition

NTILE: NTILE () splits the set of rows into the specified number of groups. It equally splits the rows for each group when the number of rows is divisible by number of group. The number is incremented for every additional group. If the number of rows in the table is not divisible by total groups count (integer_expression), then the top groups will have one row greater than the later groups. For example if the total number of rows is 6 and the number of groups is 4, the first two groups will have 2 rows and the two remaining groups will have 1 row each

Syntax:
SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] <order_by_clause>) </order_by_clause>

 

SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] )

(integer_expression) – The number of groups into which each partition must be divided.


 

 

 

It’s many times observed, a SQL Server Developer commonly use SELECT for assigning values to variables. This was fine and a perfectly valid practice till SQL Server 6.5 But SQL Server 7.0 introduced the new SET statement for initializing and assigning values to variables and SQL Server 7.0 Books Online also stated: It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.”

There are many Differences between SET and SELECT as

1) SET is the ANSI standard way of assigning values to variables, and SELECT is not.

2) SET can only assign one variable at a time, SELECT can make multiple assignments at once.

Example :

– Declaring variables

DECLARE @Variable1 AS int, @Variable2 AS int

– Initializing two variables at once

SELECT @Variable1 = 1, @Variable2 = 2

– The same can be done using SET, but two SET statements are needed */

SET @Variable1 = 1

SET @Variable2 = 2

3) If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned

4) When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from its previous value)

Please Comments on same J


There may be probability that we have hundreds of SQL Server’s Jobs scheduled to run every day on a SQL Server instance.  Here is a script to find quickly the stored procedure or command text used by SQL Server’s Job.

Select JB.Name [Job Name], JBSTP.Command [Command Text] from MSDB.dbo.Sysjobs JB
INNER JOIN MSDB.dbo.sysjobsteps JBSTP on JB.Job_ID = JBSTP.Job_ID
where JBSTP.Command Like   ‘%SP or Command Name%’

 

Its frequently happens when we need to delete a database or restore a database and few users are still connected to that specific Database, it’s through an error saying that database is used by other users. Here is a script, using this we can drop/kill all connection to a specific database.

– Let Database name is VIRENDRATEST and we want to dropp all connection of this DB.

Use Master
Go 

Declare @dbname sysname
Set @dbname =
‘VIRENDRATEST’

–Write the database name that you want to drop connections

Declare @spid int
Select @spid =
min(spidfrom master.dbo.sysprocesses where dbid = db_id(@dbname)
While @spid is 
not NULL

Begin
Execute (‘Kill ‘ + @spid)
Select @spid
min(spidfrom master.dbo.sysprocesses where dbid =db_id(@dbnameand spid > @spid

End

Print ‘All connection DROPPED!’

In SQL Server Log Shipping process, we can see two files having extension name like .TUF and .WRK. These both files only generate on Log Shipping’s Secondary Server.
Actually SQL server manage internally handle the file copy process and restoration process at Secondary server using LS Copy and LS Restore job which are created at secondary server during log shipping initialization process. To manage the file copy process from Primary server to Secondary server, .WRK files temporarily generated. Means, The .wrk file got generate when the transaction log backups files are being copied from the backup location (Commonly at Primary Server end) to the secondary server by the agent job named as LS-Copy on the secondary, and when file copied completely at secondary server, they renamed to the .trn
extension. The temporary naming using the .wrk extension indicates/ensure that the files will not picked up by the restore job until successfully copied.

The .TUF file is the Transaction Undo File. It got generated only when we have configured the Log Shipping with Stand by Option only. As we know, in Stand by Log Shipping option, Secondary Database is available to user in read mode. In this scenario .TUF file Keeps Pending Transaction Which are in Log File came from Primary server, and when next Log Backup will come from primary server they can be synchronized at Secondary Server.

Happy Reading ! You live several lives while reading  :-) 

 

The below image is a illustrative explanation of LOG SHIPPING concept.

As a DBA it may be a challenging task to gather some system level or at business level some specific or few data needs to be check at SQL Server startup level. SQL Server provides this feature where we can execute stored procedure when SQL server instance starts and as per requirement we can analyze the data. For same first check system configuration, the option ‘scan for startup procs’ should be set 1.
Steps are as below,

– Step 1 – Lets check Where ‘show advanced options’ option is configured or not, if not, first configure it as

sp_configure ‘show advanced options’ ,1
Reconfigure with override
Go 

– Step 2 – Configure ‘scan for startup procs’ option as

sp_configure ‘scan for startup procs’ ,1

Reconfigure with override
Go

From above syntax, server is now enabled to execute stored procedure at instance startup time.

As we know, when SQL Server starts, it first scans the registry to check the startup parameter values, during this it’s find the master database files and make the master database online. Now, Master Database is online, and startup procedure should be in Master Database and that SP will be execute from Master DB only. (Here is a restriction – Our SP should be in Master DB).

The sp_procoption stored procedure is used to execute the SP when SQL Server service starts as,

EXEC sp_procoption ‘procedure name’, ‘startup’, ‘true’

We can turn-off the Stored procedure execution at SQL server instance startup using below query.

EXEC sp_procoption ‘procedure name’, ‘startup’, ‘false’

Please comment on this topic.