# SQL

## 四大步驟、流程

1.連接資料庫

2.執行sql指令

3.自由發揮(畫面或流程的設計，或是DataBinding交由控制項呈現)

4.關閉資源(釋放資料、關閉資料庫連接)

## Uniqueidentifier

參考資料:(<https://docs.microsoft.com/zh-tw/previous-versions/sql/sql-server-2008-r2/ms190215(v=sql.105>))

uniqueidentifier 資料類型中儲存做為全域唯一識別碼 (GUID) 使用的 16 位元組二進位值。GUID 是一種唯一的二進位數字；全世界的電腦都不會產生重複的 GUID 值。GUID 的主要用途是在有許多站台與許多電腦的網路上指派一個唯一識別碼。

## UNION ALL

參考資料:(<https://www.1keydata.com/tw/sql/sqlunionall.html>) UNION ALL 這個指令的目的也是要將兩個 SQL 語句的結果 合併在一起。 UNION ALL 和 UNION 不同 之處在於 UNION ALL 會將每一筆符合條件的資料都列出來，無論資料值 有無重複。

## 匯出資料庫對應表

```
sqlmetal /conn:"Data Source=IP;Initial Catalog=DBName;Persist Security Info=True;User ID=AccountID;Password=AccountPassword" /views /functions /dbml:D:\FileName.dbml /context:DataBaseDataContext
```

## type = bit

如果該欄位類型為type 裡面有可能為 NULL、True、False 檢查是不是NULL

要用 field is null 不是 field = null

是否為True field = 1; 或是 field = 'True'; 以此類推

參考資料:(<https://www.sqlservercentral.com/Forums/Topic625247-145-1.aspx>)

## 資料庫新增刪除導致效能變差

當在一個表格中，新增資料、再刪除資料，會導致該表格的索引支離破碎，若該資料長度可以控制在100、1000可能還好。若太多，就會因為索引的支離破碎，導致效能的降低。

## T-SQL Insert

注意Select出來的欄位類型，跟insert的欄位類型是否一致

```
  insert into table1 (field1, field2)
  select fieldA, fieldB table2 where 條件
```

## SQL的switch

參考資料:(<https://www.1keydata.com/tw/sql/sql-case.html>)

```
select type
when 'a' then '1'
when 'b' then '2'
end
select table
```

## update join

將另一個資料庫表格內的資料，update到想要的資料庫。

```
update [DB1].[dbo].table1
  set [DB1].[dbo].table1.Name = b.Name
  from [DB1].[dbo].table1
  join DB2.[dbo].table2 as b on [DB1].[dbo].table1.ID = b.ID
```

## nchar、nvarchar

nchar 固定長度，1\~4000，資料長度大小相似時使用。 nvarchar 可變長度，1\~4000。max表示儲存體大小上限是 2^30-1。以位元組為單位的最大儲存體大小是2GB。資料長度大小可能非常不同時用。

每個非Null 的varchar、nvarchar 資料行，都需要額外的24個byte的固定配置，而不利於排序作業期間的8060個byte的資料列限制。

## 同個資料表的不同兩筆資料組字串

```
select a.field1+b.field1 as c 
  from [DB].[dbo].[Table1] as a
  join [DB].[dbo].[Table1] as b on 1=1
  where a.index = 70 and b.index = 1321
```

## With ... AS ()

參考資料:SQL With As 用法(<http://daidos200211.blogspot.com/2012/05/sql-with.html>)

參考資料:SQL With As 用法(<https://hk.saowen.com/a/154fdb8256d819efb816da42b26546309ff7a566fd94f16f9b6fb335f85eb9eb>)

參考資料:SQL 中使用 WITH AS 提高性能 － 使用公用表表达式（CTE）简化嵌套 SQL(<https://software.intel.com/zh-cn/blogs/2011/03/24/sql-with-as-cte-sql>)

#### WITH AS的涵義

**WITH AS短語，也叫做子查詢部分（subquery factoring），可以定義一個SQL片斷，該SQL片斷會被整個SQL語句用到。可以使SQL語句的可讀性更高，也可以在UNION ALL的不同部分，作為提供數據的部分。**

對於UNION ALL，使用WITH AS定義了一個UNION ALL語句，當該片斷被調用2次以上，優化器會自動將該WITH AS短語所獲取的數據放入一個Temp表中。而提示meterialize則是強制將WITH AS短語的數據放入一個全局臨時表中。很多查詢通過該方式都可以提高速度。

#### 使用方法

1.先看下面一個嵌套的查詢語句：

```
select * from person.StateProvince where CountryRegionCode in 
         (select CountryRegionCode from person.CountryRegion where Name like 'C%')
```

如果嵌套的層次過多，會使SQL語句難以閱讀和維護。

2.使用表變量使SQL語句容易維護

```
declare @t table(CountryRegionCode nvarchar(3)) 
insert into @t(CountryRegionCode)  (select CountryRegionCode from person.CountryRegion where Name like 'C%')

select * from person.StateProvince where CountryRegionCode in (select * from @t)
```

雖然語法變更複雜，但是將子查詢放在表變量 `@t` 中，會使SQL語句更容易維護。但是這會造成另外一個問題，就是**性能的損失**。

由於表變量會使用臨時表，從而增加額外的I/O開銷，因此，表變量不適合**數據量大**且**頻繁查詢**的情況。

為此，SQL Server 2005 提供另外一種解決方案 **公用表達式(CTE)**，使用CTE，既能提高維護性，效率也比表變量高得多。

## ErrorMessage: 陳述式已結束。最大遞迴 100 已在陳述式完成之前用盡。

因為預設最大遞迴為100，尾段增加這一行去修改，0為不設定。

`--OPTION (MAXRECURSION 0)`

## 資料類型

#### 精確數值

**貨幣**

money 8個位元組 (只支援兩個十進位數，而非四個)

範圍: -922,337,203,685,477.5808 到 922,337,203,685,477.5807 (-922,337,203,685,477.58 到 922,337,203,685,477.58 (Informatica)。 Informatica 只支援兩個十進位數，而非四個。)

smallmoney 4個位元組 -214,748.3648 \~ 214,748.3647 (超過此範圍用money)

**數字**

decimal

有效位數 儲存體位元組 1 - 9 5 10-19 9 20-28 13 29-38 17

#### 近似數值

float

real

### 查詢欄位Schema 待研究

```
SELECT
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM   
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('Comm_Account')
```

## SQL查詢慢的各種原因

參考資料:([http://fecbob.pixnet.net/blog/post/39249515-sql-%E6%9F%A5%E8%A9%A2%E6%85%A2%E7%9A%8448%E5%80%8B%E5%8E%9F%E5%9B%A0%E5%88%86%E6%9E%90%28%E5%88%86%E4%BA%AB%29](http://fecbob.pixnet.net/blog/post/39249515-sql-%E6%9F%A5%E8%A9%A2%E6%85%A2%E7%9A%8448%E5%80%8B%E5%8E%9F%E5%9B%A0%E5%88%86%E6%9E%90\(%E5%88%86%E4%BA%AB\)))

## Having

Having 必須在Group by 後面，Order by 之前 因為它是用來幫Group by 之後的結果，做更進一步的篩選、過濾

## 設計Date

因為時區的關係，倘若時間都直接儲存 +8 後的時間，雖然在突然要查的時候，可能會比較快跟好閱讀，但未來要做大的時候會尾大不掉。

若能在一開始就儲存最原始的資料 +0 ，或是 Timestamp，要用的時候在轉換，可以使資料更純粹和單純。

## 自動遞增 AUTO INCREMENT

參考資料:w3c(<https://www.w3schools.com/sql/sql_autoincrement.asp>) 參考資料:MySQL(<https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html>)

## Sequence

可以去參考 MariaDB 內的 Sequence，那邊也是符合 ANSI 的。