Select Top 1 with Ties Sample with Row_number or dense_rank

create table myTable (
machine_id int
, column_date date
, username varchar(50))

INSERT INTO MYTABLE values
(1,’2020-01-01′,’user1′)
,(1,’2020-03-01′,’user2′)
,(1,’2020-05-01′,’user3′)
,(1,’2020-05-01′,’user4′)

,(2,’2020-01-01′,’user4′),(2,’2020-04-01′,’user5′)
,(3,’2020-02-01′,’user6′),(3,’2020-03-01′,’user7′)

–most time use a row_number function to filter rn=1
;with mycte as (

SELECT machine_id , column_date, username
, ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY column_date DESC) as rn
FROM mytable
)

Select machine_id , column_date, username
from mycte
WHERE rn = 1 ;

–use row_number() in order by

— Post from Jeff Williams 3188
Select Top 1 With Ties
machine_id, column_date, username From myTable
Order By row_number() over(Partition By machine_id Order By column_date desc);
–use dense_rank to get the tie rows
Select Top 1 With Ties
machine_id, column_date, username From myTable
Order By dense_rank() over(Partition By machine_id Order By column_date desc);
drop table myTable

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2eabc41f-f989-4f9b-917f-9515e31ea817/need-help-with-nested-sql-query-and-top-clause?forum=transactsql#2eabc41f-f989-4f9b-917f-9515e31ea817

PIVOT Multiple Columns in a table with JSON in SQL Server(2017 or 2019)

  

 ---SQL Server 2017 or 2019
CREATE TABLE mytable(gid int, colA varchar(10) , colB int,colC date)
INSERT mytable VALUES
(1, 'test1',  11,'20200101')
,(1, 'test2',22,'20200112')
,(1, 'test3',33,'20200122')
,(2, 'test6',  120,'20200101')
,(2,' test5',122,'20190111')
,(2,'test4',3,'20200319')
,(2, 'test0',4,'20200505')
,(2, 'test99',4,'20200131')

 select * from mytable

;with mycte as (

SELECT gid, '{"data":['+ STRING_AGG(concat('{"colA":"',colA,'","colB":"',colB,'","colC":"',colC)+'"}',  ',') WITHIN GROUP ( ORDER BY colC ASC) + ']}' as jsCol

FROM mytable 

GROUP BY gid
)

Select
gid ,
jSON_VALUE(jsCol,'$.data[0].colA') as A1
,jSON_VALUE(jsCol,'$.data[1].colA') as A2
,jSON_VALUE(jsCol,'$.data[2].colA') as A3
,jSON_VALUE(jsCol,'$.data[3].colA') as A4

,jSON_VALUE(jsCol,'$.data[0].colB') as B1
,jSON_VALUE(jsCol,'$.data[1].colB') as B2
,jSON_VALUE(jsCol,'$.data[2].colB') as B3
,jSON_VALUE(jsCol,'$.data[3].colB') as B4

,jSON_VALUE(jsCol,'$.data[0].colC') as C1
,jSON_VALUE(jsCol,'$.data[1].colC') as C2
,jSON_VALUE(jsCol,'$.data[2].colC') as C3
,jSON_VALUE(jsCol,'$.data[3].colC') as C4
,jSON_VALUE(jsCol,'$.data[4].colC') as C5

--, isjson(jsCol)
-- ,jsCol

from mycte

drop TABLE mytable

  

Split Value Pair with XML or JSON using T-SQL

  

 declare @Sample table (Col  varchar(2000))
Insert into @Sample values ('MAJ=maj;Type=Evt;Site=RabodHC;Urg=0;Nom=324;API=APC;LOC=G01;IDX=3')
 ,('MAJ=maj;Type=Evt;Site=RabodHC;Urg=0;Nom=324;API=APC;LOC=G02;IDX=2')
 ,('MAJ=maj;Type=Evt;Site=RabodHC;Urg=0;Nom=324;API=APC;LOC=G03;IDX=1')

  ;with mycte as (

SELECT
ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
S.a.value('@MAJ',  'varchar(50)') as [MAJ],
S.a.value('@Type', 'varchar(50)') as [Type],
S.a.value('@Site','varchar(50)') as [Site],
S.a.value('@Urg', 'varchar(50)') as [Urg],
S.a.value('@Nom', 'varchar(50)') as [Nom],
S.a.value('@API', 'varchar(50)') as [API],
S.a.value('@LOC', 'varchar(50)') as [LOC],
S.a.value('@IDX', 'varchar(50)') as [IDX]

 FROM   (
  

<pre><code>
SELECT Col,
Cast (
N'<H><r ‘
+ Replace(Replace(Col, ‘=’,’=”‘),’;’,'” ‘) + ‘” /></H>’
AS XML) AS [vals]

</code> </pre>

FROM   @Sample)   d  

CROSS APPLY d.[vals].nodes('/H/r') S(a)
 )

select * from mycte  

  

  ---SQL Server 2016 or up
 declare @Sample table (Col  varchar(2000))
Insert into @Sample values ('MAJ=maj;Type=Evt;Site=RabodHC;Urg=0;Nom=324;API=APC;LOC=G01;IDX=3')
 ,('MAJ=maj;Type=Evt;Site=RabodHC;Urg=0;Nom=324;API=APC;LOC=G02;IDX=2')
 ,('MAJ=maj;Type=Evt;Site=RabodHC;Urg=0;Nom=324;API=APC;LOC=G03;IDX=1')

;with mycte as (
select *
,'{"'+Replace(Replace(Col, '=','":"'),';','","') +'"}' jsonInfo

from
@Sample)

SELECT Col,
--isjson(jsonInfo),
 JSON_VALUE(jsonInfo,'$.MAJ') as [MAJ],
JSON_VALUE(jsonInfo,'$.Type') as [Type],
JSON_VALUE(jsonInfo,'$.Site') as [Site],
JSON_VALUE(jsonInfo,'$.Urg') as [Urg],
JSON_VALUE(jsonInfo,'$.Nom')as [Nom],
JSON_VALUE(jsonInfo,'$.API')as [API],
JSON_VALUE(jsonInfo,'$.LOC') as [LOC],
JSON_VALUE(jsonInfo,'$.IDX')as [IDX]
from mycte

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/544af734-d8ee-430c-8918-aa7d7b930aa3/column-split-value-by-delimiters-automatic?forum=transactsql

Find Open Files on Server

My clean up job cannot delete one of old files. I need to find which process is using it that is blocking my job.

Use Computer Management to view shared folders>> open files.
You can check Open file and Accessed By from right window.

I found one of my restore job is using the file I want to remove.

To begin:
you can type compmgmt.msc in search box to open Computer Management console on your server.

Improved UDF inline Performance in SQL Server 2019

Make sure your database is in compatibility level 150.

  

USE [master]
GO
ALTER DATABASE [yourDB] SET COMPATIBILITY_LEVEL = 150
GO
  

By default, the option TSQL_SCALAR_UDF_INLINING is on.

You can check it with the following query:

  
 Use yourDB;
 select * from sys.database_scoped_configurations
 where name ='TSQL_SCALAR_UDF_INLINING'
  

You can control the option by using:

  
 Use yourDB;
--ON
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
 
--OFF
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

  

I have a query with a UDF column which runs for about 4 minutes without using this option. When turn on this option in 2019, the query finished within 40 seconds. It is quite an improvement. We do have some heavy usage in one of our applications. Hope the application performance can be improved with this upgrade.