• 1 Table 相關語法
  • 1.1 創建 table
  • 1.2 刪除 table
  • 1.3 新增紀錄
  • 1.4 查詢紀錄
    • 1.4.1 一般查詢
    • 1.4.2 多 tables 查詢
    • 1.4.3 帶鎖查詢
  • 1.5 刪除部分或所有紀錄
  • 1.6 清空所有紀錄
• 2 Sequence 相關語法
  • 2.1 創建 sequence
  • 2.2 更改 sequence
  • 2.3 獲取下一個 sequence value
• 3 Trigger 相關語法
  • 3.1 創建或更新 trigger
    • 3.1.1 由更新紀錄觸發
• 4 其他語法
  • 4.1 執行動態 SQL
• 5 查詢系統 tables
  • 5.1 查詢 table list
  • 5.2 查詢 table 既 column 結構
• 6 查看 SQL execution plan、index 使用情況（優化 SQL 用）

1CREATE TABLE tbl_person (
2	entity_id  INT          IDENTITY(1, 1),
3	name       VARCHAR(255) NOT NULL,
4	email      VARCHAR(255) NOT NULL,
5	created_at DATETIME     NOT NULL DEFAULT GETDATE(),
6	updated_at DATETIME     NOT NULL DEFAULT GETDATE(),
7
8	PRIMARY KEY (entity_id),
9	CONSTRAINT UQ_person UNIQUE (email)
10);

DROP TABLE IF EXISTS tbl_person;

INSERT INTO tbl_person VALUES ('Alice', 'alice@gmail.com', GETDATE(), GETDATE());

INSERT INTO tbl_person (name, email) VALUES ('Bob', 'bob@gmail.com');

• IDENTITY column 唔應該入，應該由 DB 自動生成。
• 如果 VALUES 前面有 column list，有 DEFAULT 既 columns 可以選擇明確提供或者唔提供，唔提供既話 DB 就會用左 CREATE TABLE 裡面 column definition 既 DEFAULT（例子中 create_at 既 default value 就係 built-in function GETDATE()）。

SELECT * FROM tbl_person;

1SELECT
2	entity_id,
3	name,
4	email,
5	created_at
6FROM tbl_person
7WHERE entity_id = 1;

SELECT *
FROM tbl_person
ORDER BY entity_id
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY; -- NEXT、FIRST 係同義

1CREATE TABLE tbl_friend (
2	entity_id        INT          IDENTITY(1, 1),
3	person_entity_id INT          NOT NULL,
4	name             VARCHAR(255) NOT NULL,
5
6	PRIMARY KEY (entity_id),
7	INDEX IX_friend (entity_id, person_entity_id)
8);
9
10INSERT INTO tbl_friend (person_entity_id, name) VALUES (1, 'Mick');
11
12SELECT
13	p.name,
14	f.name
15FROM
16	tbl_person p,
17	tbl_friend f
18WHERE
19	p.entity_id = f.person_entity_id
20AND p.name = 'Alice'

1BEGIN TRANSACTION;
2
3SELECT *
4FROM tbl_person
5WITH (
6	UPDLOCK,
7	ROWLOCK
8)
9WHERE entity_id = 2;

DELETE FROM tbl_person WHERE entity_id = 1;

DELETE FROM tbl_person;

• DELETE 係 DML 語句。
  • 所以 DELETE 係可以喺 transaction 裡面執行，自然就可以 rollback。

TRUNCATE TABLE tbl_person;

• TRUNCATE 係 DDL 語句，有別於 DELETE。
  • 所以 TRUNCATE 係唔可以喺 transaction 裡面執行，自然就唔可以 rollback。
  • TRUNCATE 所產生既 transaction log 會遠少過 DELETE。
• TRUNCATE 唔會觸發 trigger。

CREATE SEQUENCE seq_generator START WITH 1000 MINVALUE 1000 MAXVALUE 9999 CYCLE;

ALTER SEQUENCE seq_generator RESTART WITH 2000;

SELECT NEXT VALUE FOR seq_generator;

1CREATE OR ALTER TRIGGER trg_person
2ON tbl_person
3FOR UPDATE AS
4BEGIN
5	UPDATE p
6	SET updated_at = GETDATE()
7	FROM tbl_person p, inserted
8	WHERE p.entity_id = inserted.entity_id
9END;
10GO

-- 假設 values 從某個 table 取得
DECLARE @columns VARCHAR(100) = 'name, email';
DECLARE @tableName VARCHAR(100) = 'tbl_person';

EXEC('SELECT ' + @columns + ' FROM ' + @tableName);

SELECT * FROM sys.tables;

1SELECT
2    CAST(c.name AS VARCHAR) columnName,
3    CAST(typ.name AS VARCHAR) dataType,
4    c.max_length maxLength,
5    c.precision,
6    c.scale
7FROM
8    sys.all_columns c,
9    sys.tables tbl,
10    sys.types typ
11WHERE
12    c.object_id = tbl.object_id
13AND c.system_type_id = typ.system_type_id
14AND tbl.name = 'table_name'
15ORDER BY c.column_id;

• 顯示 estimated execution plan
  1. Highlight 然後 right-click 句 SQL
  2. 撳 Display Estimated Execution Plan
  3. 佢會顯示 execution plan
• 顯示 actual execution plan
  1. Right-click 輸入 SQL query 既任何空白位置
  2. 撳 Include Actual Execution Plan
  3. 每當 execute 一句 SQL，佢就會顯示 execution plan

• 如果圖示有 index seek 字眼就會用上 index
• 如果圖示有 index scan 或者 table scan 字眼就唔會用上 index。

• Index seek 比 index scan 或者 table scan 效能要好。
• SELECT *
  • 如果令 query result 既 columns 包括左個 table 定義既 indexes 冇既 columns，咁佢個 execution plan 右邊可能會出現 2 個步驟（3 個或以上既圖示），可以係 index seek 完再 RID lookup 或者 key lookup，不過實際情況要視乎個 table schema／definition。
• LIKE
  • 如果 WHERE clause 係對 VARCHAR column 用 LIKE 'xxx%'，應該可以用到 index seek。
  • 如果 WHERE clause 係對 VARCHAR column 用 LIKE '%xxx' 或者 LIKE '%xxx%'，就用唔到 index seek。
• Type conversion
  • 如果 WHERE clause 對 INT column 用 LIKE '1%'，就用唔到 index seek，而 execution plan 既 SELECT 圖示會顯示黃色三角形以及關於 type conversion 既 warning。
• WITH (FORCESEEK)
  • 呢個 hint 可以令 1 個 scan 既步驟拆開成 2 個步驟——seek 以及 lookup。
  • SELECT email FROM tbl_person WITH (FORCESEEK) WHERE email LIKE 'xxx%';

• Wildcards and Indexes
• Sargability: Why %string% Is Slow
• SQL 筆記：Index Scan vs Index Seek
• Myth: Select * is bad
• StackOverflow - Why can't I simply add an index that includes all columns?