# 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)
```
```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
```
## 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 最後的數字
```
* 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
* 交易跟著的是 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
```
checkpoint - 強制空間回收
Sign in with Wallet
Connect another wallet