提示
Microsoft Fabric Data Warehouse 是一個企業規模的關聯式倉庫，建立在資料湖基礎上，具備未來準備架構、內建 AI 及新功能。 如果你是資料倉儲新手，建議先從Fabric Data Warehouse開始。 現有的 專用 SQL 工作負載可升級至 Fabric，以取得資料科學、即時分析與報告等多項新功能。
本文提供在專用 SQL 集區中設計數據表的重要簡介概念。
判斷資料表類別
星型架構會將數據組織成事實和維度數據表。 某些資料表用於整合或暫存資料，然後再移至事實或維度資料表。 當您設計資料表時，決定資料表資料是屬於事實資料表、維度資料表還是整合資料表。 此決策會影響適當的資料表結構和分佈。
事實數據表 包含經常在交易系統中產生的量化數據，然後載入專用SQL集區。 例如，零售企業每天會產生銷售交易，然後將數據載入專用SQL集區事實數據表進行分析。
維度資料表包含可能會變更但通常不常變更的屬性資料。 例如，客戶的名稱和地址會儲存在維度資料表中，而且只有在客戶的設定檔變更時才會更新。 若要降低大型事實資料表的大小，則不需將客戶的名稱和地址放在事實資料表的每個資料列中。 相反，事實資料表和維度資料表可以共用客戶識別碼。 查詢可以聯結這兩個資料表，以關聯客戶資料與其交易記錄。
整合資料表提供了整合或暫存資料的位置。 您可以將整合資料表建立為一般數據表、外部數據表或臨時表。 例如，您可以將資料載入至暫存表格、對暫存中的資料執行轉換，然後將資料插入到生產資料表。
架構和數據表名稱
綱要是將使用方式相似的數據表分組的好方法。 如果您要將多個資料庫從內部部署解決方案移轉至專用 SQL 集區，最好將所有事實、維度和整合數據表移轉至專用 SQL 集區中的一個架構。
例如，您可以將所有數據表儲存在WideWorldImportersDW範例專用SQL集區內一個名為的架構wwi中。 下列程式碼會建立使用者定義的架構，名為wwi。
CREATE SCHEMA wwi;
若要在專用 SQL 集區中顯示資料表的組織，您可以使用事實、dim 和 int 做為數據表名稱的前置詞。 下表顯示了一些與 WideWorldImportersDW 相關的架構和數據表名稱。
| WideWorldImportersDW 數據表 | 資料表類型 | 專用 SQL 集區 |
|---|---|---|
| 市 | 維度 | wwi.DimCity |
| 訂單 | 事實 | wwi.FactOrder |
數據表持續性
數據表會將數據永久儲存在 Azure 儲存體 中、暫時儲存在 Azure 儲存體，或儲存在專用 SQL 集區外部的數據存放區中。
一般表格
一般數據表會將數據儲存在 Azure 儲存體，作為專用SQL集區的一部分。 不論會話是否開啟，數據表和數據都會保存。 下列範例會建立具有兩個數據行的一般數據表。
CREATE TABLE MyTable (col1 int, col2 int );
暫存資料表
臨時表只存在於會話的持續時間內。 您可以使用臨時表來防止其他使用者看到暫存結果，也減少清除的需求。
臨時表會利用本機存儲來提供快速的性能。 如需詳細資訊，請參閱 臨時表。
外部資料表 (部分內容可能是機器或 AI 翻譯)
外部數據表會指向位於 Azure 儲存體 Blob 或 Azure Data Lake Store 中的數據。 搭配 CREATE TABLE AS SELECT 語句使用時，從外部數據表選取會將數據匯入專用 SQL 集區。
因此，外部數據表對於載入數據很有用。 如需載入教學課程，請參閱 使用PolyBase從 Azure Blob 記憶體載入數據。
資料類型
專用 SQL 集區支援最常用的數據類型。 如需支持的數據類型清單，請參閱 CREATE TABLE 語句中的 CREATE TABLE 參考 中的數據類型。 如需使用數據類型的指引，請參閱 數據類型。
分散式資料表
專用 SQL 資料倉儲的基本功能在於它可以跨分配儲存及操作資料表的方式。 專用 SQL 集區支援三種方法來分散資料：循環配置（預設）、雜湊和複製。
雜湊分散式資料表
哈希分散式資料表會根據分散欄中的值來分配資料行。 哈希分散式數據表的設計目的是要針對大型數據表的查詢達到高效能。 選擇分配欄時，需要考慮幾個因素。
如需詳細資訊，請參閱 分散式數據表的設計指引。
複製資料表
復寫數據表具有每個計算節點上可用的數據表完整複本。 查詢會在復寫的數據表上快速執行，因為複寫數據表上的聯結不需要移動數據。 不過，復寫需要額外的記憶體，而且對於大型數據表來說並不實用。
如需詳細資訊，請參閱複寫資料表的設計指引。
循環分配表
循環分配表將資料表列平均分配到所有分佈中。 列是隨機分配的。 將數據載入循環分配表的速度很快。 請記住，查詢可能需要比其他散發方法更多的數據移動。
如需詳細資訊，請參閱 分散式數據表的設計指引。
數據表的常見散發方法
資料表類別通常會決定應選擇哪個選項來散發資料表。
| 數據表類別 | 建議的散發選項 |
|---|---|
| 事實 | 使用雜湊散佈與叢集資料行存放區索引。 在相同分配欄位上聯結兩個雜湊表時，即可提高效能。 |
| 維度 | 針對較小的資料表使用複製。 如果資料表太大而無法儲存於每個計算節點上，請使用雜湊分散式資料表。 |
| 預備 | 使用循環分配法用於暫存表。 CTAS 的載入速度很快。 一旦資料進入暫存表格，就使用 INSERT...SELECT 將資料移至生產資料表。 |
注意
如需根據工作負載採用最佳資料表散發策略的相關建議，請參閱 Azure Synapse SQL Distribution Advisor。
表格分區
分割表會根據資料範圍來儲存資料並對表中的資料列執行作業。 例如，數據表可以依日、月或年分割。 您可以透過數據分割消除來改善查詢效能，這會將查詢掃描限制為數據分割內的數據。 您也可以透過分割區切換來維護數據。 由於 SQL 集區中的數據已經分佈，因此過多的分割區可能會降低查詢效能。 如需詳細資訊，請參閱 分區指引。 當分割區切換至不是空的數據表分割區時，如果現有的數據要截斷，請考慮在 ALTER TABLE 語句中使用 TRUNCATE_TARGET 選項。 下列程式代碼會將已轉換的每日數據載入到 SalesFact 中，覆寫任何現有的數據。
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
資料行存放區索引
根據預設，專用 SQL 集區會將數據表儲存為叢集數據行存放區索引。 這種形式的數據記憶體可達到大型數據表的高數據壓縮和查詢效能。
叢集數據行存放區索引通常是最佳選擇，但在某些情況下，叢集索引或堆積是適當的儲存結構。
提示
堆表對於載入暫存數據特別有用，例如轉換成最終數據表的暫存表。
如需資料行存放區功能的清單，請參閱 資料行存放區索引的新功能。 若要改善數據行存放區索引效能，請參閱 將數據行存放區索引的數據列群組品質最大化。
統計資料
查詢最佳化工具會在建立執行查詢的計畫時，使用資料行層級統計資料。
若要改善查詢效能，取得個別資料行的統計資料非常重要，尤其是查詢聯結中使用的資料行。 自動建立統計數據 。
不會自動更新統計數據。 新增或變更大量資料列之後，更新統計資料。 例如，在載入之後更新統計數據。 如需詳細資訊，請參閱 統計數據指引。
主鍵和唯一鍵
只有在 NONCLUSTERED 與 NOT ENFORCED 同時使用時，才支援 PRIMARY KEY。 僅在使用 NOT ENFORCED 時支援唯一約束條件。 檢查 專用 SQL 集區數據表條件約束。
用於建立數據表的命令
您可以將資料表建立為新的空白資料表。 您也可以建立資料表，並使用 select 陳述式的結果填入該資料表。 以下是用來建立資料表的 T-SQL 命令。
| T-SQL 語句 | 說明 |
|---|---|
| 建立表格 | 透過定義所有資料表資料行和選項來建立空白資料表。 |
| 建立外部資料表 | 建立外部資料表。 數據表的定義會儲存在專用SQL集區中。 數據表數據會儲存在 Azure Blob 記憶體或 Azure Data Lake Store 中。 |
| 建立資料表作為選取 | 將 select 陳述式的結果填入新資料表。 資料表的資料行和資料類型是基於 SELECT 語句的結果。 匯入資料時，可以從外部資料表選取資料。 |
| 建立外部表格以選取 | 藉由將 select 語句的結果匯出至外部位置，以建立新的外部數據表。 位置為 Azure Blob 記憶體或 Azure Data Lake Store。 |
將源數據與專用SQL集區對齊
從另一個數據源載入數據，以填入專用 SQL 集區資料表。 若要執行成功的載入，源數據中的數據行數目和數據類型必須與專用 SQL 集區中的數據表定義一致。 讓數據能夠對齊可能是設計資料表中最困難的部分。
如果數據來自多個資料存放區，您可以將數據載入專用 SQL 集區，並將其儲存在整合數據表中。 一旦數據位於整合數據表中，您就可以使用專用 SQL 集區的強大功能來執行轉換作業。 備妥資料之後，您可以將它插入生產資料表中。
不支持的數據表功能
專用 SQL 集區支援其他資料庫所提供的許多數據表功能，但並非全部。 下列清單顯示專用 SQL 集區中不支援的一些資料表功能：
資料表大小查詢
注意
如需本節中查詢的準確計數，請確定 索引維護 會定期發生，並在大型數據變更之後進行。
識別每個 60 個分發中資料表所耗用空間和資料列的一個簡單方式，就是使用 DBCC PDW_SHOWSPACEUSED。
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
不過，使用 DBCC 命令可能會有相當大的限制性。 動態管理檢視 （DMV） 比 DBCC 命令更詳細。 從建立此檢視開始：
CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
GETDATE() AS [execution_time]
, DB_NAME() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [two_part_name]
, nt.[name] AS [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL)) AS [node_table_name_seq]
, tp.[distribution_policy_desc] AS [distribution_policy_name]
, c.[name] AS [distribution_column]
, nt.[distribution_id] AS [distribution_id]
, i.[type] AS [index_type]
, i.[type_desc] AS [index_type_desc]
, nt.[pdw_node_id] AS [pdw_node_id]
, pn.[type] AS [pdw_node_type]
, pn.[name] AS [pdw_node_name]
, di.name AS [dist_name]
, di.position AS [dist_position]
, nps.[partition_number] AS [partition_nmbr]
, nps.[reserved_page_count] AS [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count] AS [unused_space_page_count]
, nps.[in_row_data_page_count]
+ nps.[row_overflow_used_page_count]
+ nps.[lob_used_page_count] AS [data_space_page_count]
, nps.[reserved_page_count]
- (nps.[reserved_page_count] - nps.[used_page_count])
- ([in_row_data_page_count]
+ [row_overflow_used_page_count]+[lob_used_page_count]) AS [index_space_page_count]
, nps.[row_count] AS [row_count]
from
sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
ON t.[object_id] = i.[object_id]
AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
ON nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
ON nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
ON nt.[object_id] = nps.[object_id]
AND nt.[pdw_node_id] = nps.[pdw_node_id]
AND nt.[distribution_id] = nps.[distribution_id]
AND i.[index_id] = nps.[index_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
ON cdp.[object_id] = c.[object_id]
AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
[execution_time]
, [database_name]
, [schema_name]
, [table_name]
, [two_part_name]
, [node_table_name]
, [node_table_name_seq]
, [distribution_policy_name]
, [distribution_column]
, [distribution_id]
, [index_type]
, [index_type_desc]
, [pdw_node_id]
, [pdw_node_type]
, [pdw_node_name]
, [dist_name]
, [dist_position]
, [partition_nmbr]
, [reserved_space_page_count]
, [unused_space_page_count]
, [data_space_page_count]
, [index_space_page_count]
, [row_count]
, ([reserved_space_page_count] * 8.0) AS [reserved_space_KB]
, ([reserved_space_page_count] * 8.0)/1000 AS [reserved_space_MB]
, ([reserved_space_page_count] * 8.0)/1000000 AS [reserved_space_GB]
, ([reserved_space_page_count] * 8.0)/1000000000 AS [reserved_space_TB]
, ([unused_space_page_count] * 8.0) AS [unused_space_KB]
, ([unused_space_page_count] * 8.0)/1000 AS [unused_space_MB]
, ([unused_space_page_count] * 8.0)/1000000 AS [unused_space_GB]
, ([unused_space_page_count] * 8.0)/1000000000 AS [unused_space_TB]
, ([data_space_page_count] * 8.0) AS [data_space_KB]
, ([data_space_page_count] * 8.0)/1000 AS [data_space_MB]
, ([data_space_page_count] * 8.0)/1000000 AS [data_space_GB]
, ([data_space_page_count] * 8.0)/1000000000 AS [data_space_TB]
, ([index_space_page_count] * 8.0) AS [index_space_KB]
, ([index_space_page_count] * 8.0)/1000 AS [index_space_MB]
, ([index_space_page_count] * 8.0)/1000000 AS [index_space_GB]
, ([index_space_page_count] * 8.0)/1000000000 AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;
數據表空間摘要
此查詢會依數據表傳回數據列和空格。 您可以查看最大數據表及其分配方式：它們是回圈分配、復寫還是哈希散發。 對於以哈希分布的資料表，查詢會顯示分散列。
SELECT
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
, COUNT(distinct partition_nmbr) as nbr_partitions
, SUM(row_count) as table_row_count
, SUM(reserved_space_GB) as table_reserved_space_GB
, SUM(data_space_GB) as table_data_space_GB
, SUM(index_space_GB) as table_index_space_GB
, SUM(unused_space_GB) as table_unused_space_GB
FROM
dbo.vTableSizes
GROUP BY
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
ORDER BY
table_reserved_space_GB desc
;
依分佈類型排序的數據表空間
SELECT
distribution_policy_name
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;
依索引類型排序的數據表空間
SELECT
index_type_desc
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;
分佈空間摘要
SELECT
distribution_id
, SUM(row_count) as total_node_distribution_row_count
, SUM(reserved_space_MB) as total_node_distribution_reserved_space_MB
, SUM(data_space_MB) as total_node_distribution_data_space_MB
, SUM(index_space_MB) as total_node_distribution_index_space_MB
, SUM(unused_space_MB) as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY distribution_id
ORDER BY distribution_id
;
相關內容
在為您的專用 SQL 集區建立資料表之後，下一步是將資料載入至資料表。 如需載入教學課程，請參閱 將數據載入專用 SQL 集 區，並檢閱 Azure Synapse Analytics 中專用 SQL 集區的數據載入策略。