sql-server - Azure SQL 数据仓库表中的分区数据

Question

我正在尝试对 Azure SQL 数据仓库中的分区表进行一些操作。但是我看到了一些对我来说没有意义的事情。我显然做错了什么，但我无法弄清楚它是什么。

我的目的是用 10000 行数据填充第一个表 (Marc.foo)，检查分区元数据，然后将分区切换到第二个空表 (Marc.foo2)。

我首先创建两个分区表:

IF OBJECT_ID('Marc.foo', 'U') IS NOT NULL
  DROP TABLE Marc.foo
GO

IF OBJECT_ID('Marc.foo2', 'U') IS NOT NULL
  DROP TABLE Marc.foo2
GO

CREATE TABLE Marc.foo
(
    id int NOT NULL
)
WITH 
(   
     DISTRIBUTION = HASH (id),
     CLUSTERED COLUMNSTORE INDEX, 
     PARTITION (id RANGE RIGHT FOR VALUES (0, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000))
)
GO

CREATE TABLE Marc.foo2
(
    id int NOT NULL
)
WITH 
(   
     DISTRIBUTION = HASH (id),
     CLUSTERED COLUMNSTORE INDEX, 
     PARTITION (id RANGE RIGHT FOR VALUES (0, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000))
)
GO

然后我用 10000 行填充第一个表 (Marc.foo):

IF OBJECT_ID('tempdb..#numbers', 'U') IS NOT NULL
  DROP TABLE #numbers
GO

WITH 
    CTE_2 AS (SELECT 1 as id UNION ALL SELECT 1 as id), 
    CTE_4 AS (SELECT a.id FROM CTE_2 a, CTE_2 b), 
    CTE_16 AS (SELECT a.id FROM CTE_4 a, CTE_4 b), 
    CTE_256 AS (SELECT a.id FROM CTE_16 a, CTE_16 b), 
    CTE_64K AS (SELECT a.id FROM CTE_256 a, CTE_256 b)
SELECT      id
INTO        #numbers
FROM        CTE_64K

INSERT INTO Marc.foo(id)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM #numbers WHERE id <=10000

因为我刚刚将数据加载到表中，所以我要在表上创建统计信息:

CREATE STATISTICS stats_Marc_foo_id ON Marc.foo(id) WITH FULLSCAN

现在我检查分区元数据:

SELECT      sch.name AS [schema_name],
            tbl.[name] AS [table_name],
            ds.type_desc, 
            prt.[partition_number],
            rng.[value] AS [current_partition_range_boundary_value],
            prt.[rows] AS [partition_rows]
FROM        sys.schemas                             sch
            INNER JOIN sys.tables                   tbl ON  sch.schema_id       = tbl.schema_id
            INNER JOIN sys.partitions               prt ON  prt.[object_id]     = tbl.[object_id]
            INNER JOIN sys.indexes                  idx ON  prt.[object_id]     = idx.[object_id] AND prt.[index_id] = idx.[index_id]
            INNER JOIN sys.data_spaces              ds  ON  idx.[data_space_id] = ds.[data_space_id]
            INNER JOIN sys.partition_schemes        ps  ON  ds.[data_space_id]  = ps.[data_space_id]
            INNER JOIN sys.partition_functions      pf  ON  ps.[function_id]    = pf.[function_id]
            LEFT JOIN sys.partition_range_values    rng ON  pf.[function_id]    = rng.[function_id] AND rng.[boundary_id] = prt.[partition_number]
WHERE       sch.name = 'Marc' AND
            tbl.name = 'foo'

问题 1:这给出了我对 current_partition_range_boundary_value 的预期，但 partition_rows(我预期为 1000)为每个分区返回 5957 行。

最后，我尝试将分区 1 从 Marc.foo 切换到 Marc.foo2

ALTER TABLE Marc.foo SWITCH PARTITION 1 to Marc.foo2 PARTITION 1

我希望当我从 Marc.foo2 中选择时，我应该看到 1000 行 ID 值从 1 到 1000。但我得到零行。

问题 2:我做错了什么？

Answer 1

您的代码中有错误。您的 CTE 返回所有行的数字 1，您可以通过检查 #numbers 的内容来确认这一点。 table 。所以你的标准是id <= 10000没有效果，语句总是带回 65,536 行:

通过移动您的 ROW_NUMBER 来更正此问题向上进入 SELECT ... INTO , 例如

WITH 
    CTE_2 AS (SELECT 1 as id UNION ALL SELECT 1 as id), 
    CTE_4 AS (SELECT a.id FROM CTE_2 a, CTE_2 b), 
    CTE_16 AS (SELECT a.id FROM CTE_4 a, CTE_4 b), 
    CTE_256 AS (SELECT a.id FROM CTE_16 a, CTE_16 b), 
    CTE_64K AS (SELECT a.id FROM CTE_256 a, CTE_256 b)
SELECT      ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  AS id
INTO        #numbers
FROM        CTE_64K

我想这个故事的寓意是，不要在没有检查的情况下编写自己的数字生成例程:)