Lead and Lag in SQL Server to check the subsequent and previous value of the column

The Lead and Lag functions returns subsequent row for the Lead() while previous row for the Lag function.

Syntax

Lead(Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)

LAG (Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)

Column :

      1. Column specifies the column value to be returned or expression to return.

OFFSET :

      1. The number of rows forward from the current row from the first row. Default value for offset is 1.

Default value:

    1. The default value is set if LEAD() returns NULL.

Here we have below mentioned table records which contains Project details and their respective Business Stakeholders.

1

So Now we want to see the next project that is needed to be done once the subsequent project gets completed.

So now i want to see my subsequent project with the use of LEAP() function as shown below:

1

Using LAG() to get the previous row from the column as shown below:

1

Setting default value to the LAG and LEAD() when functions returns NULL.

We have discussed OFFSET ?(OPTIONAL),DEFAULT VALUE) in syntax. The offset value defines the number of rows forward or back with LEAD() and LAG(). While Default defines the value when function returns NULL. So in order to handle the same we will set default value ‘NoProject’

1

Happy Learning

1

RANK AND DENSE_RANK METHOD IN SQL SERVER TO RANK THE PLAYERS

Rank() and DENSE_RANK() functions are used to return the RANK starting from 1 based on the ordering clause. The RANK of row is one plus number of rows before it.Rank function skips the RANKING if there is a tie eg: 1,2,2,4,5. So here 3 is skipped due to tie in 2,2 so they both tied row will return rank as 2 and after that 4 will be returned . DENSE_RANK is also used to RANK starting from 1. The major difference between RANK() and DENSE_RANK functon is that RANK function skips the RANK is there is tie while DENSE_RANK returns the RANK without any gap.

Syntax

RANK()  OVER  ([partion by clause is optional]  ORDER BY COL1, COL2, …)

DENSE_RANK()  OVER  ( [partion by clause is optional] ORDER BY COL1, COL2, …)

Let’t take an example of below mentioned table which consist of Cricket player records for the season.

1

Now in order demonstrate to RANK the player according to their Run scored, we will makes use of RANK and DENSE_RANK function to get the difference between them, as shown below:

select NAME,TOTALSCORE,
RANK() OVER (ORDER BY TOTALSCORE DESC) [RANK],
DENSE_RANK() OVER (ORDER BY TOTALSCORE DESC) [DENSERANK]
FROM PLAYER

2

We can clearly see the difference between RANK and DENSE_RANK() once there is a tie RANK() function skips the RANK number while DENSE_RANK() returns the rank number.

Knowing the blocked queries in sql server

In order to check the blocked queries in SQL server, we can make use of DBCC OpenTran command which will result in details of the transaction which has blocked the queries.

So we have a below scenario where we are updating the records into the table using transaction as shown below:

1

Now we open a new instance of SQL server and try running SQL queries on the same table as shown below:

2

We can see that all the queries are blocked to table. So in order to check the blocking queries, we need to execute command DBCC OpenTran

script to check open transaction in SQL server:


	
SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] ASC;
GO

4

Killing open transaction using Activity monitor

5

Using Kill statement to kill the running process.

7

 

How to get rid of Jquery File reference not found in ASP.NET

I have been biting my nails out to figure out the problem of 404 error code for jquery in ASP.NET web form. After reading some article I found the legitimate example to load jquery and CSS file is as shown below:

<link type="text/css" rel="stylesheet" href='<%= Server.ResolveClientUrl("~/common/css/global.css") %>' />
 http://%#%20Page.ResolveUrl(
  <script type="text/javascript" src="<%# Page.ResolveUrl("~/Script/jquery-1.7.1.js") %>" ></script&rt