sql2005分区表示例


www.diy567.com累了,去上面休息一下下,QQ空间,美文,非主流,网络日记,搞笑短信,祝福短信,热门短信,有意思啊

 

SQL2005分区表可以在一定程度上解决海量数据的性能问题,比如可以规避高访问量数据区段的io竞争,可以缩小你查询数据范围的索引大小。 msdn参考

http://msdn.microsoft.com/zh-cn/library/ms345146.aspx 一个完整的脚本示例

--drop database dbPartitionTest

--测试数据库  

create database dbPartitionTest

go



use 

dbPartitionTest

go



--增加分组

alter database dbPartitionTest ADD FILEGROUP P200801

alter database dbPartitionTest ADD FILEGROUP P200802

alter database dbPartitionTest ADD FILEGROUP P200803

go

--分区函数

CREATE PARTITION FUNCTION part_Year(datetime)

 AS RANGE LEFT FOR VALUES 

( 

'20080131 23:59:59.997','20080229 23:59:59.997','20080331 23:59:59.997'

) 

go

--增加文件组

ALTER DATABASE dbPartitionTest ADD FILE (NAME = N'P200801',FILENAME = N'c:\tb_P200801.ndf',SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200801

ALTER DATABASE dbPartitionTest ADD FILE (NAME = N'P200802',FILENAME = N'c:\tb_P200802.ndf',SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200802

ALTER DATABASE dbPartitionTest ADD FILE (NAME = N'P200803',FILENAME = N'c:\tb_P200803.ndf',SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200803

go

--分区架构

CREATE PARTITION SCHEME part_YearScheme  AS PARTITION part_Year   TO (P200801,P200802,P200803,[PRIMARY])

go



CREATE TABLE [dbo].t_part  

(name varchar(100) default newid(),date datetime NOT NULL)

ON part_YearScheme (date)

go



--添加测试数据,每天1条

declare @date datetime

set @date='2007-12-31'

while @date<='2008-04-01'

begin

    insert into t_part(date)values(@date)

    set @date=@date+1

end

go

--查询数据分布在哪些分区

select $partition.part_Year(date) as 分区编号,* from t_part  order by date  

--查询数据库文件

go

sp_helpfile 

阅读更多

SQL2005分区表实现

05-22

alter database [hutai]rnadd filegroup [FG20070630]rnalter database [hutai]rnadd filegroup [FG20071231]rnalter database [hutai]rnadd filegroup [FG20080630]rnalter database [hutai]rnadd filegroup [FG20081231]rnalter database [hutai]rnadd filegroup [FG20090630]rngornALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20070630',rn--创建文件组rnFILENAME = N'E:\MealRecords\MealRecord0706.ndf' , SIZE = 3072KB ,rnFILEGROWTH = 1024KB ) TO FILEGROUP [FG20070630]rnALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20071231',rnFILENAME = N'E:\MealRecords\MealRecord0712.ndf' , SIZE = 3072KB ,rnFILEGROWTH = 1024KB ) TO FILEGROUP [FG20071231]rnALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20080630',rnFILENAME = N'E:\MealRecords\MealRecord0806.ndf' , SIZE = 3072KB ,rnFILEGROWTH = 1024KB ) TO FILEGROUP [FG20080630]rnALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20081231',rnFILENAME = N'E:\MealRecords\MealRecord0812.ndf' , SIZE = 3072KB ,rnFILEGROWTH = 1024KB ) TO FILEGROUP [FG20081231]rnALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20090630',rnFILENAME = N'E:\MealRecords\MealRecord0906.ndf' , SIZE = 3072KB ,rnFILEGROWTH = 1024KB ) TO FILEGROUP [FG20090630]rnrn--创建分区函数rnCREATE PARTITION FUNCTION OrderDateRangePFN(datetime)rnASrnRANGE LEFT FOR VALUES ('2007-06-30 23:59:59.997',rn '2007-12-31 23:59:59.997',rn '2008-06-30 23:59:59.997',rn '2008-12-31 23:59:59.997')rnrn--创建分区架构rnCREATE PARTITION SCHEME [MealSMSPS] rnAS PARTITION OrderDateRangePFNrnTO ([FG20070630],[FG20071231],[FG20080630],[FG20081231],[FG20090630])rn--创建分区表rnCREATE TABLE [MealRecords] (rn [id] [int] NOT NULL ,rn [ver_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,rn [pos_sequ] [int] NULL ,rn [card_sequ] [int] NOT NULL ,rn [opcard_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,rn [clock_id] [int] NOT NULL ,rn [emp_id] [nvarchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,rn [card_id] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,rn [sign_time] [datetime] NOT NULL ,rn [mark] [tinyint] NULL ,rn [flag] [tinyint] NULL ,rn [card_times] [int] NOT NULL ,rn [card_consume] [money] NOT NULL ,rn [card_balance] [money] NOT NULL ,rn [kind] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,rn [passed] [bit] NULL ,rn [mealtype] [smallint] NULL ,rn [op_ymd] [datetime] NULL ,rn [difine_sequ] [int] NULL ,rn [op_user] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,rn [id_value] [int] NULL ,rn [other_id] [int] NULL ,rn CONSTRAINT [PK_MealRecords_new] PRIMARY KEY CLUSTERED rn (rn [sign_time],rn [card_id],rn [card_times],rn [card_balance],rn [card_sequ]rn ) WITH FILLFACTOR = 95 ON [PRIMARY] rn)ON MealSMSPS([sign_time])rnrn第一次使用SQL2005的分区表,看了一天的资料,建立了如上的脚本,也成功运行了。但在导入数据的时候,发现创建的文件中并没有相应的记录。rn第一次导入采用的DTS,第二次写了个查询导入。都不行,请高手指点一二。rn

SQL2005创建分区表

10-08

数据库CJ_XSZFZServer中一张明细数据表记录太多打算用分区表rn做的步骤如下:rn1.通过:rn[code=SQL]rnAlter DataBase CJ_XSZFZServer Add FileGroup [FG201202]rn[/code]rn创建文件组rnrn2.通过:rn[code=SQL]rnAlter DataBase CJ_XSZFZServer Add File(name='FG201202',rnFilename='F:\soft\FG201202File.data',rnSize=1GB,MaxSize=unlimited,FileGrowth=1GB) to FileGroup FG201202rn[/code]rn创建数据文件rnrn3.创建分区函数rn4.创建分区架构rn5.创建分区表:rn[code=SQL]rnCreate Table G_CJFlowBillDetailrn(rn [B_ItemID] [int] IDENTITY(1,1) NOT NULL,rn [B_ID] [int] NOT NULL,rn [B_ProcessIndex] [int] NULL,rn [B_GoodsID] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,rn [B_People] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,rn [B_Qty] [float] NULL,rn [B_Price] [float] NULL,rn [B_FactSum] [float] NULL,rn [B_Memo1] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,rn [B_ProcessName] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,rn [B_Rate] [float] NULL,rn [B_NumOpe] [int] NULL,rn [B_QtyFact] [float] NULL,rn [B_Date] [datetime] NULL,rn [B_DateModify] [datetime] NULL,rn [B_CPU] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL,rn [B_ComputerName] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,rn [B_ComputerUserName] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,rn [B_IP] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,rn [B_HardWareID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULLrn)rnOn Schema_Zone_CJFlowBillDetail(B_Date)rn[/code]rnrn上面一步一步运行测试,到第4步都是通过的rn但在第5步上提示:rn数据库中已存在名为 'G_CJFlowBillDetail' 的对象。rnrnrnSQL2005 Management Studio中当前使用的数据库为CJ_XSZFZServerrn数据库CJ_XSZFZServer中是已经有表G_CJFlowBillDetail了,并且有5KW条的数据rnrn上面的错误提示要怎么解决?

没有更多推荐了,返回首页