---
# System prepended metadata

title: 2021-01-11 SQL Training Day 1

---

# 2021-01-11 SQL Training Day 1

## 00 DataType

### Working with SQL Server Data Types

* 關聯式資料庫先儲存再分析，先找對的事情，再把事情做對
* 非關聯式資料庫先分析再儲存，直接做對的事
* rowversion 判斷資料列是否已被變更
* [資料類型優先順序 (Transact-SQL)](https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15)

```sql=
SELECT 1 + '1'  -- 2
```
* 隱含轉換都是小轉大，可能對 column 進行型別轉換，造成效能問題，視情況可使用明顯轉換

### Working with Character Data

```sql Bopomofo
'丁' > '一'
```
* collate 用此決定 index 判斷 >=<
  * cs/ci > 是否區分大小寫，預設 ci 不區分
  * as/ai > 是否區分腔調字，預設 ai 不區分
  * ws/wi > 是否區分全半形，預設 wi 不區分

* null 是一種狀態，在資料庫做比較時，盡量以值來做比較
* 資料建議都加預設值

```sql=
SELECT* FROM(VALUES('anc'),('a%'),('a%%bc')) t(c1)
WHERE cl like 'a#%' escape '#' --回傳 a%

SELECT* FROM(VALUES('anc'),('a%'),('a%%bc')) t(c1)
WHERE cl like 'a[%]'
```

### Working with Date and Time Data
* date / time 要分開存
* 時間型別不要放分隔符號
```sql=
declare @d datetime = '17531231'
```
* 時間型別盡量用 date、datetime2、time、sysdatetime() 不要用 datetime、smalldatetime、getdate()
```sql=
declare @d datetime = '17531231', @d2 date = '00010101'
select datalength(@d), datalength(@d2)
```
![](https://i.imgur.com/4phWnuX.png)

```sql=
declare @d8 datetime ='20210111 23:59:59.998' --回傳2021-01-11 23:59:59.997
declare @d9 datetime ='20210111 23:59:59.999' --回傳2021-01-12 00:00:00.000
select @d8 AS d8, @d9 AS d9
```
![](https://i.imgur.com/pdLM3sE.png)

## 01 Table Expressions

### 
```sql=
cross apply
```
```sql=
SELECT f1(col) FROM f2(col) WHERE f3(col) = f4(input)
```
* 效能 差 -> 好
f3(col) -> f1(col) -> f2(col) -> f4(input)
* Fn 權限：
table-fn: select
scalar-fn: exec

* CTE --> 遞迴階層架構

## 02 TSQL_Set Operators

```sql
set statistics io on
set statistics time on
```
* 如果大的那項是 ASYNC_NETWORK_IO ，就是 AP 端要改，大資料不出資料庫，分頁 or else
```sql
select * From sys.dm_exec_session_wait_stats
where session_id=121 -- tab 最後的數字
```
![](https://i.imgur.com/whUggBM.png)

* Apply 幾乎同等於 Join

## 03 Windowing

[Windowing](https://louis176127.pixnet.net/blog/post/351472144-sql-window-function%28%E4%BA%8C%29%EF%BC%9Aaggregate-function)
```sql
SELECT [視窗函數] ( [計算欄位] ) OVER ( PARTITION BY [分組欄位]  ORDER BY [排序欄位] ) FROM [資料表] 
```

* Window Ranking Functions
```sql
  SELECT productid, productname, unitprice,
         ROW_NUMBER() OVER(ORDER BY unitprice DESC) AS ROW_NUMBERs,
         RANK() OVER(ORDER BY unitprice DESC) 		AS pricerank,
         DENSE_RANK() OVER(ORDER BY unitprice DESC) AS DENSE_pricerank,
         NTILE(3) OVER(ORDER BY unitprice DESC)     AS NTILEs
    FROM [Northwind01].[dbo].[Products]
ORDER BY ROW_NUMBERs, pricerank, DENSE_pricerank,NTiles
```

## 05 Transactions
* 硬碟 > 悲觀；memory > 樂觀
* 盡量不要分散式交易 (DTC), id = -2
![](https://i.imgur.com/ZgZ4R6P.png)

* 交易跟著的是 session, 不受 batch (go) 影響
* go 不是 SQL 語法，是 SSMS 看的

```sql
declare @i int=1
select @i
go 
select @i
```
以上語法在執行到 go 下的 select 會GG
因為第二個@i 已經不在宣告的 scpoe 裡
go 代表一個 batch

```sql
select * from t
begin t values(1)
go
insert t values(2)
commit
--執行結果 cl 1 2
```

* SP 內不要寫 trans，從 C# 裡控制，以防巢狀 SP 裡的 commit/rollback 混亂
* commit > 能 commit 的就 commit；rollback > 全 rollback
* 是 error 等級不同，決定是否會往下繼續執行；或用 set xact_abort on 讓它只要錯了一定結束，不繼續往下走

```sql
select * from t
begin tran
    insert t values(1)
    begin tran
        insert t values(2)
    commit
rollback
select @@TRANCOUNT
truncate table t
rollback
--執行結果 cl
```

```sql=
select * from t
begin tran
    insert t values(1)
    insert t values(1)
commit
select @@TRANCOUNT
truncate table t
rollback
--執行結果 cl 1
```

```sql=
select * from t
begin try
    begin tran
        insert t values(1)
        insert t values(1)
    commit
end try
begin catch
    select XACT_STATE(), ERROR_LINE(), ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_PROCEDURE(),ERROR_SVERITY(),ERROR_STATE()
end catch    
select @@TRANCOUNT
truncate table t
rollback
--執行結果 cl 1
```

```sql=
create table t(cl int)
insert t values(1)
insert t select * from t
go 20 

dbcc shrinkfile(northwind_log)
declare @rowcount int = 1
while @rowcount > 0
begin
    delete top(10000) t
    set @rowcount = @@ROWCOUNT
    checkpoint
end
```

![](https://i.imgur.com/mdjJdFu.png)
checkpoint - 強制空間回收