SQL 數據倉庫 - 是否可以將整個表切換到分區？

可能是一個愚蠢的問題，但我想知道是否可以將整個表切換為更大表的分區？

我猜不是，因為檢查約束不可用於將表約束在分區邊界上。

Azure SQL 數據倉庫不支持檢查約束，因此分區切換稍有不同。表 DDL 中描述的分區方案（而不是分區模式/函式對）必須在兩個表中完全匹配。

從這裡複製的程式碼範例：

-- Assume we have a file with the values 1 to 100 in it.

-- Create an external table over it; will have all records in
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'ext' )
EXEC ( 'CREATE SCHEMA ext' )
GO


-- DROP EXTERNAL TABLE ext.numbers
IF NOT EXISTS ( SELECT * FROM sys.external_tables WHERE object_id = OBJECT_ID('ext.numbers') )
CREATE EXTERNAL TABLE ext.numbers (
   number          INT             NOT NULL
   )
WITH (
   LOCATION = 'numbers.csv',
   DATA_SOURCE = eds_yourDataSource, 
   FILE_FORMAT = ff_csv
);
GO

-- Create a partitioned, internal table with the records 1 to 50
IF OBJECT_ID('dbo.numbers') IS NOT NULL DROP TABLE dbo.numbers

CREATE TABLE dbo.numbers
WITH (
   DISTRIBUTION = ROUND_ROBIN,
   CLUSTERED INDEX ( number ), 
   PARTITION ( number RANGE LEFT FOR VALUES ( 50, 100, 150, 200 ) )
   )
AS 
SELECT * 
FROM ext.numbers
WHERE number Between 1 And 50;
GO

-- DBCC PDW_SHOWPARTITIONSTATS ('dbo.numbers')


-- CTAS the second half of the external table, records 51-100 into an internal one.
-- As check contraints are not available in SQL Data Warehouse, ensure the switch table
-- uses the same scheme as the original table.
IF OBJECT_ID('dbo.numbers_part2') IS NOT NULL DROP TABLE dbo.numbers_part2

CREATE TABLE dbo.numbers_part2
WITH (
   DISTRIBUTION = ROUND_ROBIN,
   CLUSTERED INDEX ( number ),
   PARTITION ( number RANGE LEFT FOR VALUES ( 50, 100, 150, 200 ) )
   )
AS 
SELECT *
FROM ext.numbers
WHERE number > 50
GO


-- Partition switch it into the original table
ALTER TABLE dbo.numbers_part2 SWITCH PARTITION 2 TO dbo.numbers PARTITION 2;


SELECT *
FROM dbo.numbers
ORDER BY 1;

高溫高壓

引用自：https://dba.stackexchange.com/questions/178234