SQL SERVER 表分区

数据库 waitig 530℃ 百度已收录 0评论
--分布视图:多个表做UNION操作
--创建文件组
 --分两步走别偷懒
--给文件组创建文件
ALTER DATABASE InsideTSQL2008 ADD FILEGROUP [InsideTSQL2008_FileGroup_1]
ALTER DATABASE InsideTSQL2008 ADD FILEGROUP [InsideTSQL2008_FileGroup_2]
ALTER DATABASE InsideTSQL2008 ADD FILEGROUP [InsideTSQL2008_FileGroup_3]
ALTER DATABASE InsideTSQL2008 ADD FILEGROUP [InsideTSQL2008_FileGroup_4]
GO

--查看数据库中文件情况
select name,type_desc,physical_name,state_desc,size,growth
from sys.database_files

--给文件组添加文件
alter database InsideTSQL2008 add file
 (name=InsideTSQL2008_File_1,filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InsideTSQL2008_File_1.ndf',SIZE=5MB,
 maxsize=UNLIMITED,filegrowth=10%) to filegroup [InsideTSQL2008_FileGroup_1];
 alter database InsideTSQL2008 add file
 (name=InsideTSQL2008_File_2,filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InsideTSQL2008_File_2.ndf',SIZE=5MB,
 maxsize=UNLIMITED,filegrowth=10%) to filegroup [InsideTSQL2008_FileGroup_2];
  alter database InsideTSQL2008 add file
 (name=InsideTSQL2008_File_3,filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InsideTSQL2008_File_3.ndf',SIZE=5MB,
 maxsize=UNLIMITED,filegrowth=10%) to filegroup [InsideTSQL2008_FileGroup_3];
  alter database InsideTSQL2008 add file
 (name=InsideTSQL2008_File_4,filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InsideTSQL2008_File_4.ndf',SIZE=5MB,
 maxsize=UNLIMITED,filegrowth=10%) to filegroup [InsideTSQL2008_FileGroup_4];

--创建分区函数
--left 和right表示临界值放在左边还是右边
Create Partition Function test_steel_0330(datetime)
 as range left for values(
'20060701','20070701','20080701')
--创建分区方案
--多创建一个文件组,指定为要使用的下一个文件组
Create Partition SCHEME test_steel_0330_2
 as partition test_steel_0330
   To(
 InsideTSQL2008_FileGroup_1,
 InsideTSQL2008_FileGroup_2,--如果要在同一分区也要指定多次 (InsideTSQL2008_FileGroup_1,)
 InsideTSQL2008_FileGroup_3,
 InsideTSQL2008_FileGroup_4)
 --如果在表中创建主键或唯一索引,则分区依据列必须为该列。
 BEGIN TRANSACTION
  --创建分区表
 create table Orders (
 [orderid] [int] IDENTITY(1,1) NOT NULL,
    [custid] [int] NULL,
    [empid] [int] NOT NULL,
    [orderdate] [datetime] NOT NULL,
    [requireddate] [datetime] NOT NULL,
    [shippeddate] [datetime] NULL,
    [shipperid] [int] NOT NULL,
    [freight] [money] NOT NULL CONSTRAINT [DFT_Orders_freight]  DEFAULT ((0)),
    [shipname] [nvarchar](40) NOT NULL,
    [shipaddress] [nvarchar](60) NOT NULL,
    [shipcity] [nvarchar](15) NOT NULL,
    [shipregion] [nvarchar](15) NULL,
    [shippostalcode] [nvarchar](10) NULL,
    [shipcountry] [nvarchar](15) NOT NULL
)on test_steel_0330_2(orderdate)

 --删除索引
ALTER TABLE [Sales].[OrderDetails] DROP CONSTRAINT [FK_OrderDetails_Orders]


ALTER TABLE [Sales].[Orders] DROP CONSTRAINT [PK_Orders]

--添加索引
ALTER TABLE [Sales].[Orders] ADD  CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED 
(
    [orderid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_test_steel_0330_2_636460039345041341] ON [Sales].[Orders]
(
    [orderdate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [test_steel_0330_2]([orderdate])

--删除分区索引
DROP INDEX [ClusteredIndex_on_test_steel_0330_2_636460039345041341] ON [Sales].[Orders]

ALTER TABLE [Sales].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY([orderid])
REFERENCES [Sales].[Orders] ([orderid])
ALTER TABLE [Sales].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders]

commit

--查询分区依据列为2007-01-20的数据在哪个分区上
select $partition.test_steel_0330('2007-01-20')  --返回值是2,表示此值存在第2个分区 

--查询每个分区中的记录
--$PARTITION函数可以得到物理分区表的编号
select * from sales.Orders where $PARTITION.test_steel_0330(orderdate)=2 

--查看分区表中,每个非空分区存在的行数
select $partition.test_steel_0330(orderdate) as partitionNum,count(*) as recordCount
from sales.Orders
group by  $partition.test_steel_0330(orderdate)

--分区拆分,即增加一个边界值
alter partition function bgPartitionFun()
split range(N'20070101')  --将第二个分区拆为2个分区

--合并分区,即删除一个边界值
alter partition function bgPartitionFun()
merge range(N'20070101')  --将第二第三分区合并

--切换分区,即将已填充的表或分区与空的表或分区进行交换
alter table sales.orders switch partion 1 to test

--将普通表中的数据复制到bigorder分区表中的第一分区
--要先将分区表中的索引删除,即便普通表中存在跟分区表中相同的索引。
alter table test switch to sales.orders partition 1 

--test 需满足以下要求
--字段数量相同,对应位置的字段相同
--相同位置的字段要有相同的属性,相同的类型。
--两个表在一个文件组中

--创建表
create table test (
  orderid] [int] IDENTITY(1,1) NOT NULL,
    [custid] [int] NULL,
    [empid] [int] NOT NULL,
    [orderdate] [datetime] NOT NULL,
    [requireddate] [datetime] NOT NULL,
    [shippeddate] [datetime] NULL,
    [shipperid] [int] NOT NULL,
    [freight] [money] NOT NULL CONSTRAINT [DFT_Orders_freight]  DEFAULT ((0)),
    [shipname] [nvarchar](40) NOT NULL,
    [shipaddress] [nvarchar](60) NOT NULL,
    [shipcity] [nvarchar](15) NOT NULL,
    [shipregion] [nvarchar](15) NULL,
    [shippostalcode] [nvarchar](10) NULL,
    [shipcountry] [nvarchar](15) NOT NULL
)on [InsideTSQL2008_FileGroup_1]
--查看分区函数
select * from sys.partition_functions

--删除分区函数
drop PARTITION FUNCTION test_steel_0330
--查看已创建的分区方案
select * from sys.partition_schemes

--删除分区方案
drop PARTITION scheme test_steel_0330_2
--查看分区表信息(我没有查到)
--1.
select convert(varchar(50),ps.name) as partition_schema,
p.partition_number,
convert(varchar(10),ds2.name) as filegroup ,
convert(varchar(19),isnull(v.value,''),120) as rage_boundary,
str(p.rows,9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id=ps.data_space_id
join sys.destination_data_spaces dds on ps.data_space_id=dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id=ds2.data_space_id
join sys.partitions p on dds.destination_id=p.partition_number and p.object_id=i.object_id and p.index_id=i.index_id
join sys.partition_functions pf on ps.function_id=pf.function_id
left join sys.partition_range_values v on pf.function_id=v.function_id and v.boundary_id=p.partition_number-pf.boundary_value_on_right
where i.object_id=object_id('sales.Orders') 
and i.index_id in(0,1)
order by p.partition_number
--2.
SELECT ps.name AS PSName,  
 dds.destination_id AS PartitionNumber, 
  fg.name AS FileGroupName,fg.name,
  t.name,
  f.name as filename
  FROM (((sys.tables AS t  
  INNER JOIN sys.indexes AS i
      ON (t.object_id = i.object_id)) 
  INNER JOIN sys.partition_schemes AS ps 
      ON (i.data_space_id = ps.data_space_id)) 
   INNER JOIN sys.destination_data_spaces AS dds 
      ON (ps.data_space_id = dds.partition_scheme_id))
   INNER JOIN sys.filegroups AS fg  
       ON dds.data_space_id =fg.data_space_id
       inner join sys.database_files f on f.data_space_id = fg.data_space_id
      where t.name='sales.Orders'
--3.
select distinct 
object_name(a.object_id) tabname
,c.name colname
from sys.partitions a
inner join sys.index_columns b
 on a.object_id=b.object_id and a.index_id=b.index_id
inner join sys.columns c
 on b.object_id=c.object_id and b.index_column_id=c.column_id
where a.object_id=object_id('sales.orders') 
--4.
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%orders%'
--删除文件
--清空文件中数据
DBCC SHRINKFILE(FileName,EMPTYFILE)
--删除文件
ALTER DATABASE InsideTSQL2008 REMOVE FILE InsideTSQL2008_File_2
--删除文件组
--1.更改依赖的分区表
--2.删除分区方案
--3.删除文件组
ALTER DATABASE InsideTSQL2008 REMOVE filegroup [DB_FileGroup_20110401]
select * from sales.orders
--删除表分区可通过删除原表或将表合并至一个分区中
--备份
select * into #t from sales.orders
ALTER TABLE [Sales].[Orders]  drop  CONSTRAINT [FK_Orders_Shippers]
drop table sales.orders
create table [Sales].[Orders]


INSERT INTO [Sales].[Orders]
           (orderid,
           [custid]
           ,[empid]
           ,[orderdate]
           ,[requireddate]
           ,[shippeddate]
           ,[shipperid]
           ,[freight]
           ,[shipname]
           ,[shipaddress]
           ,[shipcity]
           ,[shipregion]
           ,[shippostalcode]
           ,[shipcountry])

    select orderid,
    [custid]
           ,[empid]
           ,[orderdate]
           ,[requireddate]
           ,[shippeddate]
           ,[shipperid]
           ,[freight]
           ,[shipname]
           ,[shipaddress]
           ,[shipcity]
           ,[shipregion]
           ,[shippostalcode]
           ,[shipcountry]
           from #t







本文由【waitig】发表在等英博客
本文固定链接:SQL SERVER 表分区
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)