Database Partitioning practices

--Query the partition of a table

Select  as  from

--time period for creating partitions

CREATE PARTITION FUNCTION part_func_userlog (datetime) as RANGE left for VALUES ('20120709 23:59:59.997', '20120710 23:59:59.997', '20120711 23:59:59.997', '20120712 23:59:59.997', '20120713 23:59:59.997', '20120714 23:59:59.997', '20120715 23:59:59.997', '20120716 23:59:59.997', '20120717 23:59:59.997','20120718 23:59:59.997');

--Create a partition grouping

ALTER DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS1]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS2]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS3]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS4]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS5]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS6]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS7]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS8]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS9]; Goalter DATABASE goodbooksadd FILEGROUP [FGGOODBOOKS10];

--Create a partition file

ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks1_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks1_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS1]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks2_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks2_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS2]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks3_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks3_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS3]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks4_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks4_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS4]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks5_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks5_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS5]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks6_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks6_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS6]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks7_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks7_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS7]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks8_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks8_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS8]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks9_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks9_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS9]; ALTER DATABASE goodbooksadd FILE (NAME=fggoodbooks10_data,filename='E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\fggoodbooks10_data.ndf', size=3MB) To FILEGROUP [FGGOODBOOKS10];

--binding grouping

CREATE PARTITION SCHEME part_func_userlog_schemeas PARTITION part_func_userlogto ([fggoodbooks1],[fggoodbooks2],[ fggoodbooks3],[fggoodbooks4],[fggoodbooks5],[fggoodbooks6],[fggoodbooks7],[fggoodbooks8],[fggoodbooks9],[ Fggoodbooks10],[primary]); GO

--Add a primary key to the table

ALTER TABLE dbo. Userlog ADD CONSTRAINT pk_userlog PRIMARY KEY nonclustered  (Userlogid)   with (Statistics_norecompute = OFF, Ignore_dup_key = OFF,          allow_row_locks = on, allow_page_locks = on) on [Primary]gocreate CLUSTERED INDEX Ix_userlog_par Titioncol on dbo. Userlog (Userlogid) with  (Statistics_norecompute = off, Ignore_dup_key = off,         allow_row_locks = ON, Allow_page_ LOCKS = on) on   part_func_userlog_scheme (createddate) GO

--Delete the table's primary key, clustered index

ALTER TABLE Userlog drop CONSTRAINT Pk_userlog

--Querying data for a specified partition

SELECT * FROM  dbo. Userlog WHERE $PARTITION. Part_func_userlog (createddate) =3

--Automatically add partitions

DECLARE @databaseName varchar (), @fileGroupName varchar (@ndfName varchar), @newNameStr varchar, @fullPa th varchar, @newDay varchar, @oldDay datetime, @partFunName varchar (), @schemeName varchar set @database Name= ' goodbooks ' set @newDay =convert (varchar (+), GETDATE (), 23)--23: By day 114: Set @oldDay =cast by Time (CONVERT (varchar (10) , DateAdd (Day,-5,getdate ()), as DateTime) set @newNameStr =replace (Replace (@newDay, ': ', ' _ '), '-', ' _ ') set @ Filegroupname=n ' G ' [email protected]set @ndfName =n ' F ' [email protected]+ ' Set @fullPath =n ' E:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\ ' [email protected]+ '. ndf ' Set @partFunName =n ' Part_func_userlog ' Set @schemeName =n ' Part_func_userlog_scheme '--create filegroup if exists (SELECT * from sys.filegroups where [ Email protected]) beginprint ' file group exists, no need to add ' endelsebeginexec (' ALTER DATABASE ' [email protected]+ ' add FILEGROUP [' [email protected]+ '] ') print ' add filegroup ' if exists (SELECT * from SYS.PARTITION_SChemes where name [email protected]) beginexec (' ALTER PARTITION scheme ' [email protected]+ ' next used [' [email&] nbsp;protected]+ '] print ' modify partition scheme ' ENDIF exists (SELECT * from Sys.partition_range_values where function_id= (select function_id from sys.partition_functions where name [email protected]) and [email protected]) beginexec (' alter partition function ' [email protected]+ ' () Split range (' ' [email protected]+ ') ') ' print ' Modify partition functions ' endend--Create ndf file if exists (SELECT * from sys.database_files where [state]=0 and ([email protected] or [email  Protected])) BeginPrint ' NDF file exists and does not need to be added ' endelsebeginexec (' ALTER DATABASE ' [email protected]+ ' Add file (NAME = ' [ Email protected]+ ', FILENAME = ' [email protected]+ '] to FILEGROUP [' [email protected]+ '] ') PRINT ' ALTER DATABASE ' [email protected]+ ' ADD FILE (NAME = ' [email protected]+ ', FILENAME = ' ' [email protected ]+ ") to FILEGROUP [' [email protected]+ '] ' print ' Newly created ndf file ' end/*--------------------the filegroups and physical files that create the database above------------------------the */--partition function if exists (SELECT * from Sys.partition_functions where name [email protected]) BeginPrint ' This modification needs to be performed before modifying the partition function ' Endelsebeginexec (' CREATE PARTITION function ' [email  protected]+ ' (DateTime) as RANGE right for VALUES (' ' [email protected]+ ') ') print ' newly created partition function ' end--partitioning scheme if exists ( SELECT * from sys.partition_schemes where name [email protected]) BeginPrint ' Here the modification needs to be executed before modifying the partitioning scheme ' endelsebeginexec ( ' CREATE PARTITION SCHEME ' [email protected]+ ' as PARTITION ' [email protected]+ ' to (' PRIMARY ', ' [email  protected]+ ') print ' newly created partition scheme ' EndPrint '---------------The following is the variable definition value displayed---------------------' print ' current database: ' [ Email protected]print ' Current date: ' [email protected]+ ' (used as a randomly generated variety of names and partition boundaries) ' print ' legal naming: ' [email  Protected]print ' filegroup name: ' [email protected]print ' ndf physical file name: ' [email protected]print ' physical file full path: ' [email  protected]print ' partition function: ' [email protected]print ' partition scheme: ' [email protected]/*--view created partition function selECT * FROM sys.partition_functions--view critical value for partition function select * from sys.partition_range_values--query partition scheme SELECT * FROM sys.partition_schemes--query table data is stored in which partition, where condition queries the data that exists in the first partition select *, $partition. Pf_savetime (partition field) as Patition from table name where $partition. Pf_savetime (partition field) =1*/go

　　

Database Partitioning practices