1 USE master --备份 2 BACKUP DATABASE NGK_Prod TO DISK =‘NGK_Prod.bak‘ WITH FORMAT 3 --恢复 4 RESTORE DATABASE NGK_Prod FROM DISK = ‘NGK_Prod.bak‘ WITH replace 5 GO 6 7 --********************文件组和文件操作********************* 8 9 --添加文件组 10 USE NGK_Prod; 11 GO 12 ALTER DATABASE NGK_Prod ADD filegroup [MCFileGroup] 13 --ALTER DATABASE NGK_Prod ADD filegroup [MCFileGroup] 14 Go 15 16 --添加文件并把其指向指定文件组 17 USE NGK_Prod; 18 Go 19 alter DATABASE NGK_Prod 20 add file( 21 name = N‘MCFile‘,--文件名 22 FileName = N‘E:\公司文件\DPS\DB\MCFile.mdf‘,--存放路径 23 SIZE = 3MB, 24 maxsize = 100MB, 25 filegrowth = 5MB 26 ) to filegroup MCFileGroup 27 Go 28 29 --修改文件 30 -- use master; 31 -- Go 32 -- alter DATABASE NGK_Prod 33 -- modify file ( name=N‘newMCFile‘,size = 20MB); --可以修改所有属性,列举即可 34 -- Go 35 36 --删除文件 37 --alter DATABASE NGK_Prod remove file newMCFile 38 39 --***********************分区函数和分区方案********************************** 40 41 --分区函数 42 use NGK_Prod 43 Go 44 create partition function partion_function_MC(datetime) --分区函数名 (指标列的数据类型) 如:datetime、int 45 as range right --右边界切分,默认为left 46 for VALUES (‘2020-01-01 00:00:00‘,‘2021-01-01 00:00:00‘) --划分边界。如时间划分(‘2003/01/01‘, ‘2004/01/01‘),两个时间界限可划分出三个分区 47 Go 48 --查看分区函数是否创建成功 49 use NGK_Prod 50 select * from sys.partition_functions 51 --drop partition FUNCTION partion_function_MC 52 --分区方案 53 use NGK_Prod 54 Go 55 create PARTITION SCHEME partition_scheme_MC 56 as PARTITION partion_function_MC 57 to ([MCFileGroup],[PRIMARY],MCFileGroup) --文件组 注意分区数要与实际分区一致 58 Go 59 --查看分区方案是否创建完成 60 use NGK_Prod 61 select * from sys.partition_schemes 62 63 --在原有的基础上添加分区(可选) 64 -- use 数据库名 65 -- go 66 -- alter partition scheme ps_OrderDate next used [FG4] //修改分区方案ps_OrderDate,定义新新分区使用FG4文件组 67 -- alter partition function pf_OrderDate() split range(‘2005/01/01‘) //修改分区函数pf_OrderDate,在末尾添加界限‘2005/01/01‘ 68 -- go 69 --为现有表设置分区方案(可选) 70 -- //为AutoBench表的InsertTime列创建新聚集索引,并绑定Scheme_DateTime分区方案 71 -- CREATE CLUSTERED INDEX IX_CreateDate ON AutoBench (InsertTime) 72 -- -- ON Scheme_DateTime (InsertTime) 73 --注:如原来主键有聚众索引要将其改为非聚集索引,才可添加新聚众索引 74 -- //删除原主键上的聚集索引PK_Product 75 -- ALTER TABLE Product DROP CONSTRAINT PK_Product 76 -- //重新创建主键非聚集索引PK_Product 77 -- ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC) 78 79 80 --定义分区表 (为新建表设置分区方案) 81 create TABLE mctable( 82 id bigint, 83 createTime datetime, 84 creator nvarchar(50) 85 ) on partition_scheme_MC(createTime) 86 87 88 89 -- 90 -- select * from NGK_Prod.sys.filegroups 91 -- select * from NGK_Prod.sys.database_files 92 -- select * from sys.partition_range_values 93 -- select function_id from sys.partition_functions 94 -- select * from sys.partition_schemes 95 -- SELECT CONVERT(datetime,CONVERT(varchar(10),GETDATE(),101) ,101) 96 97 98 99 100 INSERT INTO mctable VALUES(1, getdate(),‘1‘) 101 INSERT INTO mctable VALUES(2, ‘2021-01-02 00:00:00‘,‘1‘) 102 SELECT * FROM mctable 103 --查看每个分区数据条数 104 select $partition.partion_function_MC(CreateTime) as partitionNum,count(*) as recordCount 105 from mctable 106 group by $partition.partion_function_MC(CreateTime) 107 108 --查看每个分区详细数据 109 select * from mctable where $partition.partion_function_MC(CreateTime)=3 110 select * from mctable where $partition.partion_function_MC(CreateTime)=4
1 DECLARE 2 @fileGroupName VARCHAR(20), --文件组名(格式为:FG+@Month) 3 @fileName VARCHAR(20), --文件名(格式为:F+@Month) 4 @filePath VARCHAR(100), --文件存放路径(格式为:存放目录路径+@fileName.ndf) 5 @dataBaseName VARCHAR(20), --数据库名 6 @Month VARCHAR(10), --当前时间年月(格式为:yyyymm) 7 @schemeName VARCHAR(20), --分区方案名 8 @partFunctionName VARCHAR(20), --分区函数名 9 @limit VARCHAR(10) --分区界限(以时间分区则为时间字符串,格式为:mm/dd/yyyy) 10 11 SET @fileGroupName=‘FG201805‘ 12 SET @Month=CONVERT(varchar(10),GETDATE(),112) 13 SET @fileName=N‘MCFile‘ 14 SET @filePath=‘E:\公司文件\DPS\DB\MCFile.mdf‘ 15 SET @dataBaseName=‘NGK_Prod‘ 16 SET @schemeName=‘partition_scheme_MC‘ 17 SET @partFunctionName=‘partion_function_MC‘ 18 SET @limit=CONVERT(varchar(10),GETDATE(),101) 19 20 --语句要指明需要操作的数据库 21 if exists(select * from NGK_Prod.sys.filegroups where name=@fileGroupName) 22 begin 23 print ‘文件组存在,不需添加‘ 24 end 25 else 26 begin 27 exec(‘ALTER DATABASE ‘+@dataBaseName+‘ ADD FILEGROUP [‘+@fileGroupName+‘]‘) 28 print ‘新增文件组‘+@fileGroupName 29 end 30 31 if exists(select * from NGK_Prod.sys.database_files where [state]=0 and (name=@fileName or physical_name=@filePath)) 32 begin 33 print ‘ndf文件存在,不需添加‘ 34 end 35 else 36 begin 37 exec(‘ALTER DATABASE ‘+@dataBaseName+‘ ADD FILE(NAME =‘‘‘+@fileName+‘‘‘,FILENAME = ‘‘‘+@filePath+‘‘‘)TO FILEGROUP [‘+@fileGroupName+‘]‘) 38 print ‘添加文件‘+@fileName+‘至文件组‘+@fileGroupName 39 end 40 41 if exists(select * from sys.partition_schemes where name=@schemeName) 42 begin 43 exec(‘alter partition scheme ‘+@schemeName+‘ next used [‘+@fileGroupName+‘]‘) 44 print ‘修改分区方案,指定下一分区的文件组‘ 45 end 46 else 47 begin 48 print ‘分区方案不存在‘ 49 end 50 51 if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_functions where name=@partFunctionName)) 52 begin 53 if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where name=@schemeName) and value=CONVERT(datetime,‘‘+@limit+‘‘,101)) 54 begin 55 print ‘界限已存在‘ 56 end 57 else 58 begin 59 exec(‘alter partition function ‘+@partFunctionName+‘() split range(‘‘‘+@limit+‘‘‘)‘) 60 print ‘修改分区函数,添加划分界限为:‘+@limit 61 end 62 end 63 else 64 begin 65 print ‘分区函数不存在‘ 66 end