(转)sqlserver表分区(二)

refs:

https://www.cnblogs.com/knowledgesea/p/3696912.html

https://blog.csdn.net/leshami/article/details/4783702

创建好分区表后查看相应的信息:

1.查看分区依据列的指定值所在的分区 

--查询分区依据列为10000014的数据在哪个分区上
select $partition.bgPartitionFun(2000000) --返回值是2&＃xff0c;表示此值存在第2个分区,bgPartitionFun为分区方法 

2.查看分区表中&＃xff0c;每个非空分区存在的行数

--查看分区表中&＃xff0c;每个非空分区存在的行数select $partition.etor_ticket_partition_function(create_time) as partitionNum,count(0) as recordCount
from by_etor_tickets_1
group by  $partition.etor_ticket_partition_function(create_time)


3.查看指定分区中的数据记录 

---查看指定分区中的数据记录
select * from bigorder where $partition.bgPartitionFun(orderid)&＃61;2

结果&＃xff1a;数据从1000001开始到200W结束

    4. 查看分区的相关情况

--查看分区及分区范围的情况
select * from sys.partitions where object_id &＃61; object_id(&＃39;by_etor_tickets_fault_1&＃39;);
select * from sys.partition_range_values;

--查看分区架构情况
select * from sys.partition_schemes;

--查看某一特定分区列值属于哪个分区
select Performance.$partition.Part_func_orders(&＃39;20050325&＃39;) as partition_num;

--查看某一特定分区的记录
select * from orders where Performance.$partition.Part_func_orders(orderdate) &＃61; 2

--查看各分区所包含的记录数
select $partition.Part_func_orders(orderdate) as partition_num,
  count(*) as record_num
from orders
group by $partition.Part_func_orders(orderdate)
order by $partition.Part_func_orders(orderdate);