T-SQL

SQL Server 資料類型 (1)

基本介紹

教學目標

初步了解 SQL Server 所能使用的數值資料類型。

重點概念

在 SQL Server 中,每個資料行、區域變數、運算式和參數都有相關的資料類型,資料類型是指定物件所能保留之資料類型的屬性,其中數值資料類型可分為四大類:

  1. 整數資料類型
    主要儲存正負整數的數值資料,但沒有小數,分別為 tinyint 、 smallint 、 int 、 bigint 。

  2. 浮點數資料類型
    主要儲存浮點數的數值資料,遵循 IEEE 754 標準,分別為 float(n) 和 real 。

  3. 貨幣資料類型
    主要儲存貨幣的數值資料,以符合實際貨幣輸入的格式,分別為 money 和 smallmoney 。

  4. 精確小數資料類型
    主要儲存包含小數的數值資料,而且完全保留數值資料的精確度,分別為 decimal(p, s) 和 numeric(p, s) 。

(註: 其中 p 全名為 Precision 代表全部位數 和 s 全名為 Scale 代表小數位數,建議採用遵循 ANSI-SQL 92 標準的 decimal。)

並且根據不同的數值資料類型會有不同的資料範圍與資料大小,如下表所示。

資料類型 資料範圍 資料大小
tinyint 0 ~ 255 1 Bytes
smallint -32,768 ~ 32,767 2 Bytes
int -2,147,483,648 ~ 2,147,483,647 4 Bytes
bigint -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 8 Bytes
float(n) -1.79E+308 ~ 1.79E+308 4 Bytes(1<=n<=24) , 8 Bytes(25<=n<=53)
real -3.40E+38 ~ 3.40E+38 4 Bytes
smallmoney -214,748.3648 ~ 214,748.3647 4 Bytes
money -922,337,203,685,477.5808 ~ 922,337,203,685,477.5807 8 Bytes
decimal(p,s) -10 的 35 次方 - 1 ~ 10 的 35 次方 - 1 5 ~ 17 Bytes
numeric(p,s) -10 的 35 次方 - 1 ~ 10 的 35 次方 - 1 5 ~ 17 Bytes

最後請根據需求採用最佳的數值資料類型,例如: 若要儲存固定位數和小數的精確數值,最適合採用 decimal 或 numeric 。

相關資源

SQL Server 效能優化 (1)

基本介紹

教學目標

初步了解透過資料表的索引提高 SQL 查詢效率,更快取得查詢結果。

重點概念

索引是一種與資料表或檢視有關的磁碟內存結構,它會加快從該資料表或檢視中擷取資料列的速度。

這些索引鍵儲存在結構中 (B-tree) ,讓 SQL Server 可以快速有效地找到與索引鍵值相關的一或多個資料列。

資料表或檢視可分成兩種類型的索引

  1. 叢集
    主要是以 B 樹結構為主,叢集索引將資料表或檢視中的資料列依其索引鍵值排序與儲存,並且叢集索引的葉節點是資料分頁。

  2. 非叢集
    主要是類似 B 樹結構,非叢集索引有一個與資料列完全分開的結構,因此資料列不會依其索引鍵值排序與儲存,並且非叢集索引的葉節點是索引分頁。

(註: 如果資料表的資料量太小,則索引能夠改進的效率則有限,並且需要額外的磁碟空間和維護成本進行索引資料的更新。)

最後 SQL Server 提供 Database Engine Tuning Advisor 協助分析資料庫環境並選取適當的索引。

相關資源

SQL Server 查詢指令 (4)

基本介紹

教學目標

初步了解 T-SQL 針對不同使用者產生對應的檢視表的用途。

重點概念

檢視表是一種虛擬資料表,是由查詢定義其內容。雖然與資料表類似,可是檢視表是由一組具名資料行和資料列所組成,其用途為:

  1. 檢視角度
    對焦、簡化和自訂每位使用者查看資料庫的角度。

  2. 安全機制
    讓使用者能夠透過檢視存取資料,但不將直接存取基底資料表的權限授與使用者。

  3. 相容介面
    提供回溯相容介面以模擬其結構描述已變更的資料表。

此外將資料複製到 SQL Server 及從中複製資料時,也可以使用檢視表,來提高效能分割資料。最後 SQL Server 還提供資料庫中具有特殊用途的索引檢視表,可以大幅改善某些查詢類型的效能。

(註: 若要索引檢視表,就必須在該檢視表上建立唯一叢集索引,最適用於彙總許多資料列的查詢,不適用於經常更新的基礎資料集。)

相關資源

SQL Server 查詢指令 (3)

基本介紹

教學目標

初步了解 T-SQL 透過 SELECT 指令從 SQL Server 中的一個或多個資料表選取一個或多個資料列或資料行。

重點概念

首先 SELECT 指令是資料操作語言中語法最複雜的陳敘式,其主要子句摘要為:

1
2
3
4
5
6
[ WITH <common_table_expression>]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression]
[ HAVING search_condition]
[ ORDER BY order_expression [ ASC | DESC ] ]

接著 SELECT 指令子句都有不同的用途,說明如下表所示。

子句 說明
WITH 指定通用資料表運算式的暫存結果之名稱
SELECT 指定查詢結果的欄位資訊
FROM 指定查詢結果的來源資料表
WHERE 指定查詢結果篩選的條件
GROUPBY 指定相同欄位進行群組查詢
HAVING 指群組查詢的篩選條件
ORDERBY 指定查詢結果的欄位排序

最後透過三個示意範例簡單說明如何透過 SELECT 指令從 SQL Server 中的一個或多個資料表選取一個或多個資料列或資料行。

示意範例一

顯示每個業務人員每年的銷售訂單總數。

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

GO

示意範例二

擷取 SalesPerson 資料表中的獎金是 5000.00,且 Employee 和 SalesPerson 資料表中的員工識別碼相符的每一位員工的姓名。

1
2
3
4
5
6
7
8
SELECT DISTINCT p.LastName, p.FirstName 
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson AS sp
WHERE e.BusinessEntityID = sp.BusinessEntityID);

GO

示意範例三

會依產品識別碼來分組 SalesOrderDetail 資料表,且只會包括訂單總計超出 $1000000.00,平均訂單數量小於 3 的產品群組。

1
2
3
4
5
6
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3;

GO

相關資源

SQL Server 查詢指令 (2)

基本介紹

教學目標

初步了解 T-SQL 進行表格處理的條件約束與資料完整性的對應關係。

重點概念

當透過 T-SQL 進行表格建立修改時,可以透過引數設定條件約束,確保資料庫中的資料品質。

條件約束 (Constraints) 定義有關資料行中允許值的規則,是強制資料完整性 (Data Integrity)的標準機制。

資料完整性 說明 條件約束
實體完整性 將定義資料列作為特定資料表的唯一實體。 UNIQUE、PRIMARY KEY
值域完整性 指特定資料行的項目有效性。 CHECK
參考完整性 可在輸入或刪除資料列時,保留資料表之間已定義的關聯性。 FOREIGN KEY

相關資源