SELECT * FROM sys.filegroups WHERE name = N'FG_by_date'--查是否有这个文件组创建文件组:ALTER DATABASE dbname ADD FILEGROUP FG_by_date; --在文件组上添加文件 ALTER DATABASE dbname ADD FILE(name='FG_file1',filename='C:\FG_file1.ndf') TO FILEGROUP [FG_by_date];--创建分区函数:CREATE PARTTION FUNCTION PF_by_date(datetime) AS RANGE RIGHT FOR VALUES('2009-1-01','2009-02-01','2009-03-01');--创建分区架构:CREATE PARTITION SHCEME PS_by_date AS PARTITION PF_by_date ALL TO([FG_by_date]);
建一个文件组,一个MDF文件,一个ldf文件,多个ndf文件。
楼主肯定是操作步骤不对,漏掉了一些关键的东西,分区表的建立如下: --创建文件组 ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200810] ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200901] ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200904] ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200906] --创建文件 ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_1', FILENAME = N'C:\TXJ20081.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200810] ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_2', FILENAME = N'C:\TXJ20082.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200901] ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_3', FILENAME = N'C:\TXJ20083.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200904] ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_4', FILENAME = N'C:\TXJ20084.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200906] --创建分区函数 create PARTITION FUNCTION OrderFeeItemTest(datetime)AS RANGE RIGHT FOR VALUES ('2008-10-01','2009-01-01','2009-04-01') --创建分区结构 CREATE PARTITION SCHEME OrderFeeItemS AS PARTITION OrderFeeItemTest TO (TXJ200810, TXJ200901,TXJ200904,TXJ200906) --创建分区表 CREATE TABLE TestTable(ID INT NOT NULL IDENTITY(1,1),fCreateDate DATETIME NOT NULL,) ON OrderFeeItemS(fCreateDate) --新表中插入数据
INSERT INTO TestTable Select fCreateDate From T_OrderFeeItem
--查看某一条记录在那个分区 SELECT *, $PARTITION.OrderFeeItemTest(fCreateDate) FROM TestTable --查询该表对应的分区情况 SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('TestTable')
--在文件组上添加文件
ALTER DATABASE dbname ADD FILE(name='FG_file1',filename='C:\FG_file1.ndf') TO FILEGROUP [FG_by_date];--创建分区函数:CREATE PARTTION FUNCTION PF_by_date(datetime)
AS RANGE RIGHT
FOR VALUES('2009-1-01','2009-02-01','2009-03-01');--创建分区架构:CREATE PARTITION SHCEME PS_by_date
AS PARTITION PF_by_date
ALL TO([FG_by_date]);
--创建文件组
ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200810]
ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200901]
ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200904]
ALTER DATABASE TXJ2008 ADD FILEGROUP [TXJ200906]
--创建文件
ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_1', FILENAME = N'C:\TXJ20081.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200810]
ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_2', FILENAME = N'C:\TXJ20082.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200901]
ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_3', FILENAME = N'C:\TXJ20083.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200904]
ALTER DATABASE [TXJ2008] ADD FILE (NAME = N'FG_4', FILENAME = N'C:\TXJ20084.ndf' , SIZE = 72MB , FILEGROWTH = 1024KB ) TO FILEGROUP [TXJ200906]
--创建分区函数
create PARTITION FUNCTION OrderFeeItemTest(datetime)AS RANGE RIGHT FOR VALUES ('2008-10-01','2009-01-01','2009-04-01')
--创建分区结构
CREATE PARTITION SCHEME OrderFeeItemS AS PARTITION OrderFeeItemTest TO (TXJ200810, TXJ200901,TXJ200904,TXJ200906)
--创建分区表
CREATE TABLE TestTable(ID INT NOT NULL IDENTITY(1,1),fCreateDate DATETIME NOT NULL,) ON OrderFeeItemS(fCreateDate)
--新表中插入数据
INSERT INTO TestTable Select fCreateDate From T_OrderFeeItem
--查看某一条记录在那个分区
SELECT *, $PARTITION.OrderFeeItemTest(fCreateDate) FROM TestTable
--查询该表对应的分区情况
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('TestTable')