Migrate Database Mail to SQL Server 2019

Migrate database mail to a new SQL Server 2019 server is easy now with the help of DBATools command from your favorite machine:

Copy-DbaDbMail -Source mysql2014 -Destination mysql2019

(Use window authentication)

From your new 2019 Server, you need to refresh your account password for your profiles.

From Management>>Database Mail>>Database Mail Configuration Wizard >>Manage Database Mail accounts and profiles (second choice) >> Turn on Database Mail feature if you didn’t from the pop up window.

>> Continue to choose each Account to refresh the password value of the account to access your SMTP server (basic auth) and click finish to update the authentication information.

Finally, you can send a test email to confirm the account is working.


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.


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

  

 
---SQL Server 2017 or 2019
CREATE TABLE mytable(A varchar(10) NOT NULL, B varchar(10) NOT NULL)
INSERT mytable VALUES
(10,  -1),(50,  10),(51,  10),(52,  10),
(11,  -1),(60,  11),(61,  11),(62,  11),(63,  11),
(12,  -1),(70,  12),(71,  12),(72,  12),(73,  12),(74,  12)

;with mycte as (
SELECT (
SELECT B, Vals = JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(A, 'json'), '","') + '"]')
    FOR JSON PATH
) jsCol
FROM mytable 
where B-1
GROUP BY B
)

Select 
/*jsCol, */
JSON_VALUE(jsCol,'$[0].B') MN
,JSON_VALUE(jsCol,'$[0].Vals[0]') as S1
,JSON_VALUE(jsCol,'$[0].Vals[1]') as S2
,JSON_VALUE(jsCol,'$[0].Vals[2]') as S3
,JSON_VALUE(jsCol,'$[0].Vals[3]') as S4
,JSON_VALUE(jsCol,'$[0].Vals[4]') as S5
,JSON_VALUE(jsCol,'$[0].Vals[5]') as S6
from mycte
 
 
	 
drop TABLE mytable

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d42591a9-2b0b-4943-8fe2-a6bb9fc08c5e/query-to-produce-a-table-with-7-columns-based-on-the-values-in-a-two-column-table?forum=transactsql

An Easier Way of Pivoting Data in SQL Server

https://stackoverflow.com/questions/49574006/sql-server-query-columns-to-json-object-with-group-by