IT工程師數位筆記本

--建立兩個文件組
ALTER DATABASE Test ADD FILEGROUP BEFORE2000
ALTER DATABASE Test ADD FILEGROUP AFTER2000
ALTER DATABASE Test ADD FILEGROUP AFTER2010

--創建文件
ALTER DATABASE Test ADD FILE
(Name=N'Before2000',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Before2000.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP Before2000
ALTER DATABASE Test ADD FILE
(Name=N'After2000',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After2000.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP After2000
ALTER DATABASE Test ADD FILE
(Name=N'After2010',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After2010.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP After2010

--編寫分區函數
CREATE PARTITION FUNCTION RangeTime (DATETIME) AS RANGE LEFT FOR VALUES ('2000-01-01','2010-01-01')
--編寫分區方案,  分區方案也就是將分區函數與文件組進行一個關聯
CREATE PARTITION SCHEME RangeSchema_CreateTime
AS PARTITION RangeTime
TO (BEFORE2000,AFTER2000,AFTER2010)

--創建分區表，先建表，再設主鍵，否則會出錯
CREATE TABLE Order
    (
      ID VARCHAR(50) ,
      UserId VARCHAR(50) ,
      CreateTime DATETIME
    )
ON  RangeSchema_CreateTime(CreateTime)

--測試某個對象放在哪個分區里
SELECT  $PARTITION.RangeTime('2001-2-2')

--查看某個分區表里存放的數據
SELECT  *
FROM    shop
WHERE   $PARTITION.RangeTime(CreateTime) = 1

--查看某個分區表的個數
SELECT  $PARTITION.RangeTime(CreateTime) AS 分區編號 ,
        COUNT(id) AS 記錄數
FROM    shop
GROUP BY $PARTITION.RangeTime(CreateTime)