舉例我們有一個用戶的表及一個用戶對應的所屬組別表，其中兩者的關係為One-To-Many。我們可以使用下面的方法將組別的資訊處理成Json在輸出中作為一項數據輸出，並在後續以JsonConvert.DeserializeObject來取回列表。

SELECT UserName, (SELECT GroupName FROM Group WHERE Group.UserId = Users.UserId FOR JSON AUTO) AS UserGroupJson FROM Users

JsonConvert.DeserializeObject<List<Group>>(UserGroupJson);

當我們進行寫入(Insert)/更新(Update)/刪除(Delete)的動作時，可以使用Transaction包起這些Statement，當中有任何錯誤Exception的時候可以讓我們把當中所有的改動取消及Rollback。
而當我們使用BEGIN TRANSACTION的時候將會在@@TRANCOUNT中加上一個Transaction，使用ROLLBACK時會將當前在@@TRANCOUNT中所有的Transaction清除及歸零。
如果將Transaction Commit後，@@TRANCOUNT將會減一。
以下是一段使用Try-Catch和Transaction的例子：

BEGIN TRANSACTION;  
  
BEGIN TRY  
    -- Generate a constraint violation error.  
    DELETE FROM Production.Product  
    WHERE ProductID = 980;  
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
  
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO  

• Using TRY...CATCH in a transaction
• T-SQL TRANSACTION - 巢狀交易原來是要這樣思考的阿！？
• When it's necessary to check @@trancount > 0 in try catch block?
• Exception handling and nested transactions

In SQL Server / TSQL, the trigger will fire as statement trigger, which means it will fire even there are no record affected
It caused lock of resource and long loading time for executing the trigger
For Optimizing DML Triggers, Microsoft officially suggested to check the affected row and return if no row affected

IF (ROWCOUNT_BIG() = 0)
RETURN;

ROWCOUNT_BIG() will return the number of rows affected by the last statement executed
Remember to put this at the begining of the trigger without any statement before, and before the SET NOCOUNT ON (if any)

CREATE TRIGGER statement (PL/SQL)
CREATE TRIGGER statement (oracle)

CREATE TRIGGER triggerName
{ AFTER | NO CASCADE BEFORE } 
{ INSERT | DELETE | UPDATE [ OF columnName [ , columnName ]* ] }
ON tableName
[ referencingClause ]
[ FOR EACH { ROW | STATEMENT } ] [ MODE DB2SQL ] 
triggeredSQLStatement

[1]: Use system_user of suser_name()

Reminder: this will return the domain of the user as well (domain_name\user_name)

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
  AND ROUTINE_TYPE='PROCEDURE'

Adding the columns into index could speed up the JOIN & SELECT query.

• Speeding up Joins with Indexes
• SQL index overview and strategy

byte[] bytes = Encoding.UTF8.GetBytes(json);

DECLARE @Json varbinary(max)
COMPRESS(@Json)

CONVERT(varchar(max), DECOMPRESS(CAST(Json as varbinary(max))))

string json = Encoding.UTF8.GetString(fileBytes);

可以在SSMS同時輸出中英文

SELECT CAST(CONCAT('<?xml version="1.0" encoding="UTF-8" ?><![CDATA[',DECOMPRESS(varbinaryJson),']]>') AS XML)

select * 
from 
(values ('test-a1', 'test-a2'), ('test-b1', 'test-b2'), ('test-c1', 'test-c2')) 
x(col1, col2)

• How to select several hardcoded SQL rows?

Microsoft SQL Server Integration Services (SSIS) not supported in Free version of SQL Express

• DBCC
  Shows each table level scan result, provide value like: Fragmentation, Pages, Avg. Bytes Free per Page, Avg. Page Density (full)

Find reference SQL file Database_File_Latency_Check.sql for getting the average tot latency for each database

The code reference from Disk Latency for SQL Server Database and Transaction Log Files and
Identifying SQL Server Disk Latency
The latency ms and corresponding description

dbatools.io provided tool Test-DbaDiskSpeed which can be executed on PowerShell and return the performance and latency on I/O read & write

# Sample from https://theserogroup.com/dba/identifying-sql-server-disk-latency/
Test-DbaDiskSpeed -SqlInstance localhost -SqlCredential sa | Format-Table -Property Database, SizeGB, FileName, FileID, FileType, DiskLocation, Reads, AverageReadStall, ReadPerformance, Writes, AverageWriteStall, WritePerformance, 'Avg Overall Latency' | Out-String -Width 4096 |out-file c:\temp\DbaDiskSpeed.txt

Find reference SQL file Table_Index_Fragmentation_Check.sql

-- Format
ALTER INDEX [INDEX_KEY] ON [TABLE_NAME] REBUILD
-- Sample
ALTER INDEX PK__AMDItemD__727E838BA714CE58 ON AMDItemDetail REBUILD

Troubleshoot slow SQL Server performance caused by I/O issues
RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失
RDS SQL Server - 最佳实践 - 高CPU使用率系列之二索引碎片
[笔记整理]SQL Server 索引碎片 和 重建索引
对数据库磁盘驱动器SQL Server进行碎片整理
[SQL SERVER][Memo]邏輯碎片這檔事(1)
Day10_淺談Index與FRAGMENTATION
Day1_TempDB特性與效能簡介
数据表出现了碎片化，严重影响性能怎么办？
MySQL 碎片问题

To execute DBCC in SSMS, you may need to enable SQLCMD Mode

Enable SQLCMD Scripting in Query Editor

Execute DBCC SHOWCONTIG

Check the Scan Density (掃描密度) for each table, there are fragmentation if the value is less than 100
Logical Scan Fragmentation (邏輯掃描碎片) with 0% is the best result

If fragmentation is less than 5%, it is not worthy to perform reindex or index defrag

Execute DBCC DBREINDEX to perform reindex on all index
New index will be created and replace the old index
Advantages:

• Rebuild the statistics
• Can work faster with multi-processor on large and table with high fragmentation

Disadvantage:

• Take the table to Offline when reindexing
• Require storage space

Execute DBCC INDEXDEFRAG to perform reindex on specific index
Advantages:

• Smaller performance affect than reindex
• Better work in production environment with no downtime

Disadvantage:

• Slow when working with large index
• Dependcy on the server

RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失
RDS SQL Server - 最佳实践 - 高CPU使用率系列之二索引碎片
MySQL表碎片化（Table Fragmentation）以及处理

• ApexSQL (Not Free)
• Best FREE SQL Server Tools for Database Developers and Administrators!