[SQL Server]SQL Server 2016支援的資料遮罩

以前在做資料遮罩時，幾乎都是要自己寫些程式才能實作出來，

現在SQL Server 2016就有提供遮罩的功能供大家使用，以下為用實例來為大家介紹用法:

首先先建立範例資料:

CREATE TABLE Cust(
  ID varchar(11) primary key,
        Name nvarchar(10),
  Birth date,
  Mail varchar(50),  
        Credit varchar(19))
GO

INSERT INTO Cust 
 VALUES ('A123456789', N'陳一', '19700101', 'aa@gmail.com', '1111-2222-3333-4444')
      , ('D234567890', N'蔡三', '19790202', 'bb@gmail.com', '5555-6666-7777-8888')
      , ('H987654321', N'老王', '19810303', 'cb@gmail.com', '9999-1111-2222-3333')

建立測試資料後，先針對生日欄位設定預設的遮罩功能

ALTER TABLE Cust ALTER COLUMN Birthday ADD MASKED WITH(FUNCTION='default()')

接著再建立使用者masktester，讓他有select的權限，再用他來查詢資料

CREATE USER masktester WITHOUT LOGIN
GRANT SELECT ON Cust TO masktester

EXEC AS USER = 'masktester'
SELECT * FROM Cust

查詢結果如下，原本的生日都變成1900-01-01了!

以上是基本的遮罩應用。

現在我們來做些進階應用，例如Email、信用卡卡號等重要資訊，

可用partial,email()等參數來進行客製化遮罩。

ALTER TABLE Cust ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(4,"-XXXX-XXXX-",4)')
ALTER TABLE Cust ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

EXEC AS USER = 'masktester'
SELECT * FROM Cust
REVERT

遮罩效果如下:

SQL Server 2016提供的遮罩方式是By User來進行控管的，

因此在實際應用上要針對不同的User來設定是否需要開啓或關閉遮罩。

--關閉遮罩
GRANT UNMASK TO masktester
--恢復遮罩
REVOKE UNMASK TO masktester

[SQL Server]使用DMV找出最消耗CPU/IO的SQL指令

在實務上經常會定期找出消耗CPU/IO成本比較大的Top 10 Query來進行語法調教，
以增進DB的效能和AP查詢回應的速度，以下介紹常用到的二種DMV:

--查出前十名總累積IO數最高的的Query
SELECT TOP 10
total_logical_reads+total_logical_writes AS [總IO],
execution_count AS [執行次數],
total_logical_reads AS [讀取IO數],
total_logical_writes AS [寫入IO數],
st.text AS [SQL指令]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes> 0
ORDER BY [總IO] DESC

查詢結果如下:

--查出前十名總使用CPU時間最高的的Query
SELECT  TOP 10 
qs.total_worker_time AS [總執行時間],
qs.execution_count AS [執行次數],
qs.total_worker_time / qs.execution_count AS [平均執行時間],
SUBSTRING(qt.text, qs.statement_start_offset / 2, 
(CASE WHEN qs.statement_end_offset = -1 THEN len(CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS [SQL指令]
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [總執行時間] DESC;

查詢結果如下:

SQL指令調教的方法不外乎是新增缺乏的索引或定期維護索引，
再者可以針對擁有大量資料量的表格進行資料定期刪除，
又或者可將少被使用的歷史資料(例如:半年前或上一季)切到其它張表封存，
等到有需要查詢再進行存取，這作法也是可以有效的減少查詢回應的時間。

[SQL]把找到的資料轉成HTML格式

在SQL實務應用上，相信不少人都會把抓出來的特定資料再透過email寄出來吧?

此時如果能轉成HTML格式會更方便閱讀，以下將分享使用FOR XML方式來達成此需求。

--建立測試資料
IF OBJECT_ID('#TT','U') IS NOT NULL
 DROP TABLE #TT
CREATE TABLE #TT 
(
   id INT,
   name VARCHAR(20),
   sex char(1),
   memo VARCHAR(50),
   cr_date datetime default(getdate())
)

INSERT INTO #TT 
(id,name,sex,memo)
VALUES
(1,N'John',N'M',N'test11111'),
(2,N'Lee',N'F',N'test22222'),
(3,N'Penny',N'F',N'test33333'),
(4,N'Wade',N'M',N'test444444'),
(5,N'James',N'M',N'test55555')

DECLARE @Content VARCHAR(MAX)
--將所有欄位名稱設成TD，再以''分隔
SET @Content = CAST((SELECT name AS 'TD','',
CASE sex WHEN 'M' THEN '男性' WHEN 'F' THEN '女性' END as 'TD','',
memo AS 'TD',''
FROM #TT
FOR XML PATH('tr'),TYPE) AS VARCHAR(MAX))

DECLARE @HTML VARCHAR(MAX)
SET @HTML='<HTML><BODY><TABLE border=1><TH>姓名</TH><TH>性別</TH><TH>MEMO</TH>'+@Content+'</TABLE>'

SELECT @HTML

---

參考:https://docs.microsoft.com/zh-tw/sql/relational-databases/xml/use-path-mode-with-for-xml