澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

2008提供的表分区向导,SQL普通表转分区表的方法

2019-09-29 作者:数据库网络   |   浏览(106)

汇总篇:

表分区(Partition Table)是自从SQL Server 2005就开始提供的功能,解决的问题是大型表的存储和查询。

 第一步、创建文件和文件组

复制代码 代码如下:

第一次引入文件组的概念:

图片 1 图片 2 图片 3 图片 4

-- 准备工作,模拟数据。

 

insert into

test

values

('test','2014-01-01') , 

('test','2014-02-01') , 

('test','2014-03-01') , 

('test','2014-04-01') , 

('test','2014-05-01') , 

('test','2014-06-01') , 

('test','2014-07-01') , 

('test','2014-08-01') , 

('test','2014-09-01') , 

('test','2014-10-01') 

 

select *  from User_DB.dbo.test  

 

 

CREATE TABLE Sale( 
    [Id] [int] identity(1,1) NOT NULL,          --自动增长
    [Name] [varchar](16) NOT NULL, 
    [SaleTime] [datetime] NOT NULL, 
    constraint [PK_Sale] primary key clustered  --创建主键
    ( 
        [Id] ASC 
    ) 

--插入一些记录
insert Sale ([Name],[SaleTime]) values ('张三','2009-1-1')   
insert Sale ([Name],[SaleTime]) values ('李四','2009-2-1')   
insert Sale ([Name],[SaleTime]) values ('王五','2009-3-1')   
insert Sale ([Name],[SaleTime]) values ('钱六','2010-4-1')   
insert Sale ([Name],[SaleTime]) values ('赵七','2010-5-1')   
insert Sale ([Name],[SaleTime]) values ('张三','2011-6-1')   
insert Sale ([Name],[SaleTime]) values ('李四','2011-7-1')   
insert Sale ([Name],[SaleTime]) values ('王五','2011-8-1')   
insert Sale ([Name],[SaleTime]) values ('钱六','2012-9-1')   
insert Sale ([Name],[SaleTime]) values ('赵七','2012-10-1')   
insert Sale ([Name],[SaleTime]) values ('张三','2012-11-1')   
insert Sale ([Name],[SaleTime]) values ('李四','2013-12-1')   
insert Sale ([Name],[SaleTime]) values ('王五','2014-12-1')

上次说了其他的解决方案(),就是没有说水平分库,这次好好说下。

我们之前大致的语法是这样的

## -- 1、添加文件组

 

2008提供的表分区向导,SQL普通表转分区表的方法。alter database User_DB add filegroup know1 

alter database User_DB add filegroup know2

alter database User_DB add filegroup know3  

 

alter table Sale drop constraint PK_Sale 
--创建主键,但不设为聚集索引
alter TABLE Sale add constraint PK_Sale primary key nonclustered

    [ID] ASC 
) ON [PRIMARY]    

 

-- =========================

## -- 2、创建数据库文件到文件组

 

alter database User_DB add file    

(name=N'know1',filename=N'G:DBfenquknow1.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)

to filegroup know1

 

 

alter database User_DB add file    

(name=N'know2',filename=N'G:DBfenquknow2.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)

to filegroup know2

 

 

alter database User_DB add file    

(name=N'know3',filename=N'G:DBfenquknow3.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)

to filegroup know3  

 

## --3、右键到要分区的表>> 存储--- >> 创建分区--- >>显示向导视图--- >> 下一步--- >> 下一步

 

--创建分区函数
GO
create partition function [pf_Sale](SaleTime) as range left for values(N'2010-5-1T00:00:00.000', N'2012-9-1T00:00:00.000'
GO

上次共享的第一份大数据,这次正好来演示一下水平分库

-- 演示:陈希章

# 第二步、执行生成的创建分区sql

 

USE [User_DB]

GO

BEGIN TRANSACTION

--创建分区结构
GO
create partition scheme [pt_Sale] as partition [pf_Sale] TO ([Sale1], [Sale3], [Sale2])
GO

1.模拟部分数据

-- 如何创建分区函数

## -- 1、创建分区函数

CREATE PARTITION FUNCTION [knowPartitionFunc](datetime) 

AS RANGE

LEFT FOR VALUES (N'2014-01-01T00:00:00', N'2014-02-01T00:00:00', N'2014-03-01T00:00:00')

 

--创建一个使用[pt_Sale]架构
GO
create table [dbo].[AvCache](
[Id] [int] identity(1,1) NOT NULL,          --自动增长
    [Name] [varchar](16) NOT NULL, 
    [SaleTime] [datetime] NOT NULL, 
)on [pt_Sale](SaleTime) --注意这里使用[pt_Sale]架构,根据SaleTime 分区

图片 5

-- 如何创建分区架构

## -- 2、创建分区解决方案

CREATE PARTITION SCHEME [konwPartitionSolution]

 AS PARTITION [knowPartitionFunc] TO ([PRIMARY], [know1], [know2], [know3])

 

-- 查看使用情况
SELECT *, $PARTITION.[pt_Sale](SaleTime)
 FROM dbo.[AvCache]

2.创建索引后,发现可以根据日期来分组

-- 如何创建分区表

## -- 3、创建索引

CREATE CLUSTERED INDEX [ClusteredIndex_on_konwPartitionSolution_635706905441367386] 

ON [dbo].[test] 

(

    [time]

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

 ON [konwPartitionSolution]([time])

 

图片 6

--=========================

## -- 4、删除索引

DROP INDEX

 [ClusteredIndex_on_konwPartitionSolution_635706905441367386]

  ON [dbo].[test] WITH ( ONLINE = OFF )

 

COMMIT TRANSACTION  

 

按数据量大致分一下

alter database adventureWorks add filegroup [fg1]

# 第三步、查看分区结果

## --查看分区表中,每个非空分区存在的行数

 

select $partition.[knowPartitionFunc](time) as partitionNum,count(*) as recordCount

from test

group by  $partition.[knowPartitionFunc] (time)

 

 

 

## --查看分区数据

select $partition.[knowPartitionFunc](time) as partitionGroupId,*

from test 

 

 

图片 7

go

# 第四步、为已存在的分区表添加新的边界值

步入正轨

alter database adventureWorks add filegroup [fg2]

## --1、创建文件组和对应的文件。

alter database User_DB add filegroup know4

 

alter database User_DB add file    

(name=N'know4',filename=N'G:DBfenquknow4.ndf',size=5Mb,filegrowth=5mb, maxsize=1gb)

to filegroup know4

 


go

## --2、给分区方案添加文件组

alter partition scheme konwPartitionSolution

NEXT USED know4

 

GUI方法:

alter database adventureWorks add filegroup [fg3]

## --3、给分区函数添加一个边界值

alter partition function knowPartitionFunc()

split range(N'2014-04-01T00:00:00') 

3.0创建文件组

go

图片 8

alter database adventureWorks

添加文件到文件组

add file

图片 9

(name='fg1',

命令操作:

filename='c:fg1.ndf',

alter database BigData_TestInfo_PartialData add filegroup Info

size=5mb)

图片 10

to filegroup [fg1]

alter database BigData_TestInfo_PartialData add file(name=N'TestInfo2006',filename=N'G:SQLDataBigData_TestInfo2006.ndf') to filegroup Info

go

注意:BigData_TestInfo2006.ndf是数据库自己创建的,不需要自己手动创建(有些同志手动创建了,然后报错。。。。呃,有点哭笑不得了)

alter database adventureWorks

图片 11

add file

查询看看:select * from sys.filegroups

(name='fg2',

图片 12

filename='d:fg2.ndf',

水平分区走起:一般就几步,1.创建分区函数 2.创建分区方案 3.创建分区表

size=5mb)

GUI方法

to filegroup [fg2]

图片 13

go

图片 14

alter database adventureWorks

图片 15

add file

分区函数

(name='fg3',

图片 16

filename='e:fg3.ndf',

分区方案

size=5mb)

图片 17

to filegroup [fg3]

图片 18

go

上一张图有些人可能不懂,用PPT画张概念图

use adventureWorks

图片 19

go

创建脚本

Create partition function emailPF(nvarchar(50)) as range right for values ('G','N')--创建分区函数

图片 20

go

图片 21

Create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)--创建分区方案

系统生成脚本:

go

use [BigData_TestInfo_PartialData]
go

begin transaction

create partition function [CreatedatePartitionFun](varchar(10)) as range right for values(N'2006-01-01', N'2007-01-01', N'2009-01-01', N'2012-01-01')

create partition scheme [CreatedatePartitionScheme] as partition [CreatedatePartitionFun] TO ([Info], [Info], [Info], [Info], [primary])

alter table [dbo].[Info] drop constraint [PK__Info__3214EC07B2FE10C8]

alter table [dbo].[Info] add primary key nonclustered 
(
 [Id] 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) ON [PRIMARY]


set ansi_padding on

create clustered index [ClusteredIndex_on_CreatedatePartitionScheme_636193166313125124] on [dbo].[Info]
(
 [CreateDate]
)with (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [CreatedatePartitionScheme]([CreateDate])


drop index [ClusteredIndex_on_CreatedatePartitionScheme_636193166313125124] on [dbo].[Info]

commit transaction
go

Create table customermail (custid int, email nvarchar(50)) on emailPS(email)--创建分区表

 

Go

命令方式创建(根据上面生成的命令逆推)

 

创建分区函数和架构(方案)

 

create partition function CreatedatePartitionFun(varchar(10)) as range right for values(N'2006-01-01', N'2007-01-01', N'2009-01-01', N'2012-01-01')

为了简化操作,SQL Server 2008中为表分区提供了相关的操作

图片 22

图片 23

create partition scheme CreatedatePartitionScheme as partition [CreatedatePartitionFun] TO ([Info], [Info], [Info], [Info], [primary])

图片 24

 

图片 25

创建分区表

图片 26

尚未创建表的情况

图片 27

图片 28

图片 29

已经创建了表(基本上都是这种情况)

这个向导有些诡异,居然希望表里面已经有数据。(费解)

主要就两步,把主键变为非聚集索引 创建分区聚集索引

图片 30 

图片 31

图片 32

alter table Info drop constraint PK__Info__3214EC064B338648

最后生成的脚本是这样的

alter table Info add constraint PK_Info_Id primary key nonclustered (Id asc)

USE [demo]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [ordersfunction](date) AS RANGE LEFT FOR VALUES (N'2008-01-01', N'2008-02-01', N'2008-03-01')

图片 33

CREATE PARTITION SCHEME [ordersscheme] AS PARTITION [ordersfunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

图片 34

CREATE CLUSTERED INDEX [ClusteredIndex_on_ordersscheme_633765890752500000] ON [dbo].[Orders]
(
    [OrderDate]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ordersscheme]([OrderDate])

create clustered index IX_Info_CreateDate on Info(CreateDate) on CreatedatePartitionScheme(CreateDate)

DROP INDEX [ClusteredIndex_on_ordersscheme_633765890752500000] ON [dbo].[Orders] WITH ( ONLINE = OFF )

图片 35

COMMIT TRANSACTION

 

这里要注意一个语法,因为现在表已经存在了,那么就不能再通过CREATE TABLE的方式来创建分区表了,而是通过创建一个聚集索引的方式。但又把它删除掉。

测试:基本上是均匀分散在各个文件中,生产环境的时候可以把这些文件放各个磁盘

但是,如果表上面已经有一个聚集索引呢?肯定会出错,因为一个表只能有一个聚集索引。那么该怎么办呢?

图片 36

我们发现向导会这样做,先把原先的聚集索引改为非聚聚的。这很有点意思嘛

参考文章:

ALTER TABLE [dbo].[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) ON [PRIMARY]

 

除了提供了创建分区的向导之外,还有一个管理分区的向导,主要是可以做SWITCH,MERGE,SPLIT这些操作。也可以查看数据

图片 37

图片 38

图片 39 

 

除了这些图形化工具的支持之外,SQL Server 2008的分区表还有不少增强,诸如性能等等方面

http://www.cnblogs.com/lykbk/p/erererert343243434388773437878.html

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:2008提供的表分区向导,SQL普通表转分区表的方法

关键词: