Kite的筆記
0%

SQL Server 心得筆記

發表於 更新於 分類於
13k 23 分鐘

1. Function

1.1. 字串相加方法1

1
2
3
4
5
6
7
8
9
10
11
12
13
drop function Concat_Name 
go

create function Concat_Name(@cs varchar(1000))
returns varchar(3000)
as
begin
declare   @String varchar(8000)=''
select @String=@String +name + ';'  from students where class=@cs	
return @String
end
 
select dbo.Concat_Name('B')

image-20210809170549015

![image-20210809170602581](E:\kite_project\SQLSERVER\SQL Server.assets\image-20210809170602581-16284999637694.png)

1.2. 字串相加方法2

1
2
3
4
5
6
7
 select 
(
	SELECT cast(name AS NVARCHAR ) + ',' 
    from students  	
	where class='A'	--把name一樣的加起來
	FOR XML PATH('')
) as Concat_Name

2. Trigger

2.1. 新增

先創建LOG表

1
SELECT '' as op,* INTO students_log FROM students WHERE 1=2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/****** Object:  Trigger [dbo].[students_InsertTrigger]    Script Date: 2021/8/9 下午 05:36:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[students_InsertTrigger] ON [dbo].[students]

FOR INSERT

AS
	DECLARE @op char(1);
	DECLARE @create_date datetime;
	DECLARE @id int;	
	DECLARE @student_name nvarchar(50);
	DECLARE @age int;
	DECLARE @cs nvarchar(50);
	
    -- 取出新增紀錄
    SELECT 
	@create_date=[create_date],@id=[id],@student_name=[student_name],@age=[age],@cs=[class]
    FROM inserted;

    -- 存到 LOG 檔案內
    INSERT INTO [students_log]([op],[create_date],[id],[student_name],[age],[class])
	VALUES('I',@create_date,@id,@student_name,@age,@cs);
GO

test insert sql 

1
2
   INSERT INTO [students]([create_date],[id],[student_name],[age],[class])
VALUES(getdate(),15,'aa',1,'C');

2.2. 修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE TRIGGER [dbo].[students_UpdateTrigger] ON [dbo].[students]

FOR UPDATE

AS
	DECLARE @op char(1);
	DECLARE @create_date datetime;
	DECLARE @id int;	
	DECLARE @student_name nvarchar(50);
	DECLARE @age int;
	DECLARE @cs nvarchar(50);
	
    -- 修改後
    SELECT 
	@create_date=[create_date],@id=[id],@student_name=[student_name],@age=[age],@cs=[class]
    FROM inserted;


    -- 存到 LOG 檔案內
    INSERT INTO [students_log]([op],[create_date],[id],[student_name],[age],[class])
	VALUES('U',@create_date,@id,@student_name,@age,@cs);

	--修改前
    SELECT 
	@create_date=[create_date],@id=[id],@student_name=[student_name],@age=[age],@cs=[class]
    FROM deleted;


    -- 存到 LOG 檔案內
    INSERT INTO [students_log]([op],[create_date],[id],[student_name],[age],[class])
	VALUES('D',@create_date,@id,@student_name,@age,@cs);

GO

test update sql

1
update students set student_name = '溜爺'

image-20210811105333032

3. Store Procedure

優點

  1. 預存程式可封裝，並隱藏複雜的商業邏輯。增加其隱蔽性, 安全性。

可以將資料邏輯和商務規則 (Business Rule) 加以封裝，如此使用者就只能以開發人員和資料庫系統管理員預期的方式來存取資料及物件。

  1. Stored Procedure 是已經編譯過的, 所以執行效率快

  2. 減少程式與資料庫的連結次數

  3. 預存程式可以回傳值，並可以接受參數。

  4. 預存程式無法使用 SELECT 指令來執行，因為它是子程式，與檢視表，資料表或使用者定義函式不同。

  5. 預存程式可以用在資料檢驗，強制實行商業邏輯等。

  6. 可以使用會驗證所有使用者輸入的參數化預存程序來防堵 SQL 插入式攻擊(SQL Injection)。 如果有使用動態 SQL，請務必將命令參數化，也絕對不要在查詢字串中直接包含參數值。

  7. 可以拒絕臨機操作 (Ad Hoc) 的查詢和資料修改作業。 如此可避免使用者因惡意或不慎而損毀資料，或執行會降低伺服器或網路效能的查詢。

  8. 可以處理程序程式碼中的錯誤，而不必將錯誤直接傳遞至用戶端應用程式。 如此可避免傳回可能助長探查攻擊的錯誤訊息。 請在伺服器上記錄及處理錯誤。

  9. 預存程序只需寫入一次，即可由許多應用程式存取。

  10. 用戶端應用程式不需要對基礎資料結構有任何了解。 您可以對預存程序程式碼進行變更，而不需變更用戶端應用程式，只要這些變更不會影響參數清單或傳回的資料型別即可。

  11. 預存程序可以將多項作業結合成單一的程序呼叫，藉此降低網路流量。

缺點

  1. 移植性差。預存程式，往往客製化於特定的資料庫上，因為支援的程式語言不同。當切換到其他廠商的資料庫系統時，需要重寫原有的預存程式。

  2. 對Server的負擔較大

  3. 預存程式的效能調校與撰寫，受限於各種資料庫系統。

img

假設四個不同應用系統都在Local端 所運行的這區塊程式功能都是做一樣的事情時
當一有功能調整
此時你會有需要對四個地方去做同樣調整
共需要做四次更改

這是違背開發上降低重複動作
設計觀念的

4. 常見用法

4.1. 暫存表功能

待寫

4.2. 查詢

4.2.1. 從子查詢結果寫入新表

1
2
3
4
5
SELECT * INTO KITE 
FROM 
(
SELECT * FROM ITEMS WHERE 1=1 /* condition */
) AS A

資料表較大時，可以先取出某一部分進行查詢數據，效能比較快

4.2.2. 以客戶資料為主table，查詢最後一筆交易產品線記錄

1
2
3
4
SELECT AA001 AS 客戶代號
,(SELECT TOP 1 PRODUCT_DATE FROM ORDER_BOM WHERE OD_AA001=AA001 ORDER BY PRODUCT_DATE DESC) 最後一次交易日
FROM CRMAA
WHERE AA013 IN('台北')

常見狀況以主表資料為主，再去撈取某一資訊，放在欄位上進行呈現

當然，如果有多個欄位，用一樣的語句結構，再多寫幾行子查詢

4.2.3. 子表完成後，再進行第二次篩選

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT A.*
,HC050 訂單合約起日,HC051  訂單合約迄日
FROM 
(
    SELECT AA001 AS 客戶代號,AA002 AS 客戶名稱,OD004 AS 出貨日期,OD005 AS 出貨單號
    FROM ORDER_BOM
    JOIN CRMAA ON AA001=OD_AA001
    WHERE 
    1=1
    GROUP BY OD_AA001
) AS A 
LEFT JOIN CRMHC ON HC002 = REPLACE(出貨單號,'DS2-','')
ORDER BY 出貨日期,客戶代號

縮限資料後，再進行第二次的篩選

4.2.4. 子表完成後，進行第三次篩選

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT AR001,AR002,AR003,AR004,PE2.PE02,PE2.PE03,PE2.PE04 FROM 
(
	SELECT * FROM SERAR_KITE2
	WHERE RTRIM(AR001)+RTRIM(AR002)+RTRIM(AR003)+RTRIM(AR004) 
	IN
	(
	SELECT RTRIM(AR001)+RTRIM(AR002)+RTRIM(AR003)+RTRIM(AR004) 
	FROM SERAR_KITE2
	GROUP  BY AR001,AR002,AR003,AR004 HAVING COUNT(PE02) >1
	) 
) AS AR
JOIN DSCPE PE2 ON AR.PE02=PE2.PE02 collate database_default
ORDER BY AR001,AR002,AR003,AR004

同樣概念也可以包N層

4.2.5. 縱向轉直向

1
2
3
4
SELECT 
COUNT(CASE class WHEN 'a' THEN class END) as A_CLASS_CNT
,COUNT(CASE class WHEN 'b' THEN class END) as B_CLASS_CNT
FROM students

image-20210809163459420

image-20210809163528735

同一個表統計時，如果有COUNT需求，要轉向，是可以這樣寫的

4.2.6. 相乘

1
2
3
SELECT student_name,subject_name 
FROM students,subjects
ORDER BY student_name,subject_name

image-20210809171947986

image-20210809172246152

自行想像需求

4.3. 修改

4.3.1. 參考table更新至目的table

1
2
3
4
UPDATE AR1 SET AR1.AR005= AR2.PE02
--SELECT AR1.AR005,AR2.PE02
FROM SERAR_KITE AR1 JOIN SERAR_KITE2 AR2 ON AR1.AR001= AR2.AR001 AND AR1.AR002=AR2.AR002 AND AR1.AR003=AR2.AR003 AND AR1.AR004=AR2.AR004
WHERE AR1.AR005 !=AR2.PE02

4.4. 新增

4.4.1. 批次新增資料，參考既有table，更新目的table

1
2
3
4
5
6
7
8
9
--一般業務
DECLARE @JOB_ID AS VARCHAR(10)
DECLARE @UID AS VARCHAR(10)
SET @JOB_ID='001' --一般業務
SET @UID='02944'  -- 參考工號
INSERT INTO ADMML (ML001,ML002,ML003,ML004,ML005,ML006,ML007,ML008,ML009,ML010)
SELECT DISTINCT @JOB_ID AS ML001,MG002 AS ML002, MG003 AS ML003,MG004 AS ML004,MG005 AS ML005,MG006 AS ML006,MG007 AS ML007,'' AS ML008,MG009 AS ML009,MG010 AS ML010
FROM ADMMG_ACP WHERE MG001=@UID
ORDER BY MG002

批次新增資料，再利用參數方式去當條件

4.4.2. 批次新增資眼，excel 來源匯入，更新目的table

1
2
3
4
5
6
INSERT INTO CRMHG(HG001,HG005,HG002,HG032,HG033,HG024,HG021,HG007,HG010,HG012,HG026,HG008,HG011)
SELECT 客戶代號,姓名,'8888',職稱,職能別,郵區,公司地址,電話1,電話1_分機,行動電話,行動電話2,電話2,電話2_分機
FROM [dbo].[工作表_kite] 
WHERE 
是否匯入='Y' 
AND (客戶代號 collate database_default + 姓名 collate database_default) NOT IN (SELECT HG001+HG005 FROM CRMHG)

匯入EXCEL到SQLSERVER之後，要注意的是定序問題

4.5. 日期格式轉換

1
CONVERT(VARCHAR(12),CRMCC.CREATE_DATE,112)

常見YYYYMMDD格式

4.6. 跨DB連線

1
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source = 10.40.40.232;User ID= xx; Password = xxx'). db.table

Link Server

待寫

4.7. 字元說明

char(13) = Enter

char(10) = 折行

char(32) = 空格

char(255) = null

5. 維運相關

sp_lock

sp_who

sp_who2

5.1. 效能語法

5.1.1. SQL SERVER 2000

5.1.1.1. 列出最初導致一連串其它處理序被鎖住的起始源頭
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
IF EXISTS(SELECT * FROM master.dbo.sysprocesses WHERE spid in (SELECT blocked FROM master.dbo.sysprocesses))
 BEGIN
  SELECT spid 處理序, status 狀態
  , 登入帳號=SUBSTRING(SUSER_SNAME(sid), 1, 30)
  , 使用者機器名稱=SUBSTRING(hostname, 1, 12)
  , 是否鎖住=CONVERT(char(3), blocked)
  , 資料庫名稱=SUBSTRING(DB_NAME(dbid), 1, 10)
  , cmd 命令, waittype 等待類型
  FROM master.dbo.sysprocesses
  --找出鎖住別(自己未被鎖住(blocked=0) 但在別的處理序中blocked欄位出現的值)
  WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
  AND blocked = 0
 END
ELSE
 BEGIN
   SELECT '沒有處理序被鎖住'
 END

5.1.2. SQL SERVER 2008

5.1.2.1. 找出最耗用IO的語法
1
2
3
4
5
6
7
8
9
10
11
12
SELECT TOP 10
total_logical_reads,
total_logical_writes,
execution_count,
total_logical_reads+total_logical_writes AS [IO_total],
st.text AS query_text,
db_name(st.dbid) AS database_name,
st.objectid AS object_id
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_total] DESC
5.1.2.2. 列出目前最耗用CPU的前50個查詢
1
2
3
4
5
6
7
8
9
10
SELECT  TOP 50
qs.total_worker_time / qs.execution_count AS[Avg CPU Time],
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 query_text,
qt.dbid,
qt.objectid
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [Avg CPU Time] DESC;
5.1.2.3. 列出目前最耗用Worker Time的前50個查詢
1
2
3
4
5
6
7
8
9
10
11
SELECT   TOP 50 sum(qs.total_worker_time) AS total_cpu_time,
sum(qs.execution_count) AS total_execution_count,
count(*) AS '#_statements',
qt.dbid,
qt.objectid,
qs.sql_handle,
qt.[text]
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
GROUP BY qt.dbid, qt.objectid, qs.sql_handle, qt.[text]
ORDER BY sum(qs.total_worker_time) DESC, qs.sql_handle;
5.1.2.4. 取得 SQL Server 資料庫正在執行的 T-SQL 指令與詳細資訊
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
				ltrim(q.text),
				r.statement_start_offset/2+1,
				(CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
			CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
			LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc
5.1.2.5. 找出來因為封鎖(Blocked Lock)造成的等待時間、被封鎖的連線等等之相關資訊。
1
2
3
4
5
6
SELECT session_id N'工作階段識別碼',status N'要求的狀態',
command N'目前所處理命令的類型',blocking_session_id N'封鎖要求之工作階段的識別碼' ,
wait_type N'被封鎖的等候類型',wait_time N'等候的持續時間',
wait_resource N'目前等候的資源',transaction_id N'要求執行所在交易的識別碼'
FROM sys.dm_exec_requests
ORDER BY 1 DESC

5.2. 踢掉所有用戶

1
2
use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
1
ALTER DATABASE YourDatabase SET MULTI_USER

5.3. 清LOG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
BACKUP LOG DSC_CRM_SP WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('DSC_CRM_SP_Log', 2)

DBCC SHRINKFILE ('DSC_CRM_SP_Log' ,0,TRUNCATEONLY) 

USE DSC_CRM_TEST
CHECKPOINT
DBCC SHRINKFILE(2, 200)

BACKUP LOG [DSC_CRM_TEST] WITH TRUNCATE_ONLY;
DBCC SHRINKFILE ([DSC_CRM_TEST_Log],500);


ALTER DATABASE DSC_CRM_SP SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DSC_CRM_SP_Log, 1)
ALTER DATABASE DSC_CRM_SP SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP LOG [Quiz] WITH TRUNCATE_ONLY;
DBCC SHRINKFILE ([Quiz_Log],500);

5.4. 查詢table使用空間

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SET NOCOUNT ON 

--http://msdn.microsoft.com/zh-tw/library/ms188414.aspx
--更新目前資料庫中之所有物件的頁面及 (或) 資料列計數
DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 
 
--把每個Table使用的資訊存到#t之中
INSERT #t EXEC sys.sp_MSforeachtable 'EXEC sp_spaceused ''?''' 

--依使用空間較大的依序排列並顯示MB
SELECT *
, LTRIM(STR(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' AS reservedSize_M
, LTRIM(STR(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' AS dataSize_M
, LTRIM(STR(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' AS indexSize_M
FROM #t
ORDER BY CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) DESC

-- 顯示總共筆數及總共使用資訊
SELECT SUM(CAST([rows] AS int)) AS [rows]
, LTRIM(STR(SUM(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' AS sumOfreservedSize_M
, LTRIM(STR(SUM(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' AS sumOfdataSize_M
, LTRIM(STR(SUM(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' AS sumOfindexSize_M
FROM #t
 
DROP TABLE #t

6. 複寫機制

7. SQL Profile

8. 效能ISSUE

9. 資料庫維護計劃

10. SSDT

11. ETL

img

ETL 真的沒啥好介紹的，概念真的太簡單好懂。

  • Extract: 把資料從某個地方拿出來。
  • Transform: 將資料作轉換。
  • Load: 湊數的一步，把轉換完的資料丟到某個地方。

ETL 之所以必要在於，原始資料通常不適合拿來直接使用的。原因在於：

  • 原始資料量太大：通常前端在收資料的時候，都會使用比較鬆散的事件格式，像是 JSON，也通常不會做壓縮。這樣在計算時需要消耗較多的資源，也會影響計算速度。所以通常在使用之前會做最基本的壓縮和格式最佳化。
  • 資料不乾淨：通常資料前端收進來的時候，如果沒有處理好，會遇到很多奇怪的狀況，「Garbage in、garbase out」所以確保資料的品質非常重要。比較基本的像是格式、數值範圍，更複雜的一點包括欄位統計值甚至邏輯等等。
  • 比較複雜的 Aggregation：資料是隨著事件進來，但是使用上可能會以每小時、每天為單位來進行分析或計算。如果每次畫報表都必須從最原始的資料開始算，會消耗大量計算資源，而且 SQL 或計算也會非常複雜，所以一般來說都會先將原始資料聚合成一些時間粒度稍微「粗」一點的資料，才會來做後續應用。

以下從網路上找幾個應用範例：

11.1. 標準的清理資料流程

img

一般來說，資料在進入 Data Warehouse 前，都必須經過這幾個階段，才能供後續業務報表或是視覺化使用。算是基本起手式吧。

11.2. 在不同層（Layer）的資料之間作轉換

img

資料傳送到後端後，會經過 ETL 放到 Staging 環境、再透過 ETL 放到 DW、再透過 ETL 整理到了 Data Mart 給 End User 使用。當原始資料越複雜、資料量越多，中間就更需要多層次的處理來確保資料品質以及使用上的效能。

11.3. Streaming ETL

img

當然除了 Batch ETL 外，Streaming ETL 也是有的。可以根據事件、或是 mini batch 的方式來處理原始資料，配合 Batch Process 來處理累積的資料。

總之 ETL 是個很廣泛的領域，不僅僅是「資料處理」四個字而已。還需要考量到軟硬體架構、商務邏輯、後續的使用才能設計最適合的 ETL 流程。

12. 參考連結

Data Data Pipeline 101（三）—ETL

SQL ServerTrigger的簡單範例，以「訂單的流程系統」為例