SQLServer 表分區

SQL SERVER 2005中，終於引入了表分區，就是說，當一個表裡的資料很多時，可以將其分拆到
多個的表裡，大大提高了效能。下面舉例子說明之

比如，在C盤下建立如下幾個目錄
C:\Data2\Primary
C:\Data2\FG1
C:\Data2\FG2
C:\Data2\FG3
C:\Data2\FG4

其中primary存放的是主要資料庫檔案，其他FG1--FG4存放四個單獨的檔案組，可以見創立資料庫
Data Partition DB2，如下

USE [master] GO /****** Object:  Database [Data Partition DB]    Script Date: 10/08/2006 23:09:53 ******/ IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB2') DROP DATABASE [Data Partition DB2] GO CREATE DATABASE [Data Partition DB2] ON PRIMARY (NAME='Data Partition DB Primary FG', FILENAME= 'C:\Data2\Primary\Data Partition DB Primary FG.mdf', SIZE=5, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG1] (NAME = 'Data Partition DB FG1', FILENAME = 'C:\Data2\FG1\Data Partition DB FG1.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG2] (NAME = 'Data Partition DB FG2', FILENAME = 'C:\Data2\FG2\Data Partition DB FG2.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG3] (NAME = 'Data Partition DB FG3', FILENAME = 'C:\Data2\FG3\Data Partition DB FG3.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG4] (NAME = 'Data Partition DB FG4', FILENAME = 'C:\Data2\FG4\Data Partition DB FG4.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ) 接下來，我們建立分區表函數，這其實可以理解為一個規則，說明如何以一個規則來將一個表來劃分，如下
 use [Data Partition DB2] GO CREATE PARTITION FUNCTION [Data Partition Range](int) AS RANGE LEFT FOR VALUES (100,200,300) 其中分區函數的名稱是Data Partition Range,後面的類型（int)表明接下來用來分區的那個欄位的類型是INT類型，

而VALUES (100,200,300)表明，將把表分為4個區了，是從負數到100，100-200，200-300，大於300。

接下來，我們要建立分區架構，即將分區函數應用到我們分好的四個檔案組裡面去

USE [Data Partition DB2] go CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range] TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]);

再建立表的結構
USE [Data Partition DB2]

go
CREATE TABLE MyTable
(ID INT NOT NULL, Date DATETIME, Cost money)    
   ON [Data Partition Scheme] (ID);

這裡注意，ON [Data Partition Scheme] (ID);表明，劃分時以ID的大小作為劃分的根據，ON後要跟分區架購的名稱

最後，我們可以填充資料了

USE [Data Partition DB2] go declare @count int set @count =-25 while @count <=100 begin insert into MyTable select @count,getdate(),100.00 set @count=@count+1 end set @count =101 while @count <=200 begin insert into MyTable select @count,getdate(),200.00 set @count=@count+1 end set @count =201 while @count <=300 begin insert into MyTable select @count,getdate(),300.00 set @count=@count+1 end set @count =301 while @count <=400 begin insert into MyTable select @count,getdate(),400.00 set @count=@count+1 end set @count =401 while @count <=800 begin insert into MyTable select @count,getdate(),500.00 set @count=@count+1 end 

最後，我們可以查詢下，插入的這些資料，是否真的被劃分到四個不同的檔案組裡的表分區了，可以這樣看
SELECT *, $PARTITION.[Data Partition Range](ID)
 FROM MyTable