SQL Server partition

I. Development history of the subregion

The table-based partitioning feature provides flexibility and better performance for simplifying the creation and maintenance of partitioned tables. Functions that trace back to the logical partition table and the manual partitioning table.

Two. Why do you want to partition

To improve the scalability and manageability of large tables and tables with various access patterns.

Large tables, in addition to the size measured in hundreds of GB or even terabytes, can be data tables that cannot be run as expected, running costs or maintenance costs exceeding the predetermined requirements. For example, performance issues, blocking issues, backups.

Three. Create a partition implementation

Add four filegroups to the test library

1 --Step One: Create four filegroups2 Alter DatabaseTestAddFilegroup ByIdGroup13 Alter DatabaseTestAddFilegroup ByIdGroup24 Alter DatabaseTestAddFilegroup ByIdGroup35 Alter DatabaseTestAddFilegroup BYIDGROUP4

--Step two: Create four ndf files, corresponding to each filegroup, filename file storage pathALTER DATABASETestADD FILE(NAME='File1', FILENAME='C:\Program Files\Microsoft SQL Server\mssql10. MSSQLSERVER\MSSQL\DATA\TESTFILE1.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP ByIdGroup1ALTER DATABASETestADD FILE(NAME='File2', FILENAME='E:\TESTFILE2.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP ByIdGroup2ALTER DATABASETestADD FILE(NAME='File3', FILENAME='E:\TESTFILE3.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP ByIdGroup3ALTER DATABASETestADD FILE(NAME='File4', FILENAME='E:\TESTFILE4.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP BYIDGROUP4

When the execution is complete, look as shown:

-- Step Three: Create partition functions (boundary values for each partition) CREATE FUNCTION pf_upbymemberid (intas left forVALUES (N ' 9707 ', n'9708', n'9709', n' 10600 ')

After execution, as shown:

--Fourth step: Create a partition scheme CREATE PARTITION SCHEME Ps_upbymemberidas PARTITION pf_upbymemberid to ([PRIMARY], [byidgroup1],[byidgroup2],[ BYIDGROUP3],[BYIDGROUP4])

After execution, as shown:

--Fifth step: Create a partitioned table

Right-click the table you want to partition--Save--Create partition--select partition column (here Upbymemberid)--Select partition Function--partitioning scheme

--sixth step to create a partitioned index /*create < index classification > indexes < index name > on < table name > (column name) on < partition scheme name > (partition by column name) */create Nonclustered INDEX Ixupbymemberid on [dbo].  Pub_stocktestbak (    [Upbymemberid]) with (sort_in_tempdb = off, Ignore_dup_key = off, drop_existing = off, ONLINE = off) On [Ps_upbymemberid] ([Upbymemberid])

　　

Benefits of SQL Server partitioning:

1. When tables and indexes become very large, partitions can divide data into smaller, easier-to-manage sections.
2. Reduce index maintenance time.
3. The common where condition field is better to partition on.
4. Parallel operations get better performance and can improve performance for large-scale operations in very large datasets, such as millions of of rows.
5. In general, the number of filegroups is preferably the same as the number of partitions. Filegroups allow you to place individual tables on different physical disks

SQL Server partition