时间戳

首页 > 解决方案 > 了解分区表上的非聚集索引

sql-server - 了解分区表上的非聚集索引

问题描述

我创建了一个表，我使用 partition schema 进行了水平分区PS_INTERVENCION([partitionKey])。

然后我向它添加了一个非聚集索引：

CREATE NONCLUSTERED INDEX [IX_INTERVENCION] 
ON [MEMORIA].[INTERVENCION] ([ID_INTERVENCION] ASC,
                             [ID_PERSONA] ASC,
                             [FECHA_REPORTE] ASC,
                             [COD_TURNO] ASC,
                             [partitionKey] ASC)
   WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

该索引是在相同的分区方案上创建的：

ON PS_INTERVENCION([partitionKey])

然后当我去检查这个表上的分区时，我发现了这些对象：

在此处输入图像描述

使用这个查询

select 
    schema_name(c.schema_id) [schema name],
    object_name(a.object_id),
    a.name [index name], a.type_desc [index type]
from 
    (sys.indexes a 
inner join 
    sys.tables c on a.object_id = c.object_id)
inner join 
    sys.data_spaces b on a.data_space_id = b.data_space_id
where 
    b.type = 'PS'

此外，如果我检查分区的边界，所有内容都会出现重复：

在此处输入图像描述

使用此查询：

SELECT 
    v1.boundary_value,
    CONVERT(NVARCHAR(103), SUBSTRING(v1.boundary_value, 6, LEN(v1.boundary_value))) AS Origin,
    rows
FROM 
    sys.tables AS tbl
JOIN 
    sys.indexes AS indx ON indx.object_id = tbl.object_id
JOIN 
    sys.partition_schemes ps ON indx.data_space_id = ps.data_space_id
JOIN 
    sys.partition_functions pf ON ps.Function_id = pf.Function_id 
JOIN 
    sys.partitions p ON p.object_id = indx.object_id
                     AND p.index_id = indx.index_id
LEFT JOIN 
    sys.partition_range_values prt 
CROSS APPLY
    (VALUES (CONVERT(NVARCHAR(103), prt.value))) v1(boundary_value)
    ON prt.function_id = pf.function_id
    AND prt.boundary_id + CASE WHEN pf.boundary_value_on_right = 1 THEN 1 EKD 0 END = p.partition_number

我的问题是：我的表分区了多少次？是否为每个分区创建了非聚集索引？

这种双重性的意义是什么？

标签： sql-servertsql

解决方案

您可能在该查询中弄乱了连接。试试这个：

SELECT 
  tbl.name table_name,
  indx.name index_name,
  prt.value boundary_value,
  p.partition_number,
  prt.boundary_id,
  prt.value boundary_value,
  p.rows
FROM 
  sys.tables AS tbl
  JOIN sys.indexes AS indx 
    ON indx.object_id = tbl.object_id
  JOIN sys.partition_schemes ps 
    ON indx.data_space_id = ps.data_space_id
  JOIN sys.partition_functions pf 
    ON ps.Function_id = pf.Function_id 
  JOIN sys.partitions p 
    on p.object_id = indx.object_id
    and p.index_id = indx.index_id
  LEFT JOIN sys.partition_range_values prt 
    on prt.function_id = pf.function_id
    and prt.boundary_id + case when pf.boundary_value_on_right = 1 then 1 else 0 end = p.partition_number

推荐阅读