UnPivot is used to convert columns into rows.
We have below mentioned table who columns we want to convert into rows.
Tournament in ([UTU],[Zonal])
) as UnPivotex
The Lead and Lag functions returns subsequent row for the Lead() while previous row for the Lag function.
Lead(Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)
LAG (Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)
Column specifies the column value to be returned or expression to return.
The number of rows forward from the current row from the first row. Default value for offset is 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.
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:
Using LAG() to get the previous row from the column as shown below:
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’
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.
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.
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:
RANK() OVER (ORDER BY TOTALSCORE DESC) [RANK],
DENSE_RANK() OVER (ORDER BY TOTALSCORE DESC) [DENSERANK]
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.
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:
Now we open a new instance of SQL server and try running SQL queries on the same table as shown below:
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:
[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]
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
[s_es].[session_id] = [s_tst].[session_id]
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
[s_er].[session_id] = [s_tst].[session_id]
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
[Begin Time] ASC;
Killing open transaction using Activity monitor
Using Kill statement to kill the running process.
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") %>' />