采用GUID分區方法
/*
版本
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/
USE [master]
go
IF EXISTS ( SELECT name FROM master. dbo. sysdatabases WHERE name = N'DEMO' )
DROP DATABASE [DEMO]
go
CREATE DATABASE [DEMO]
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_1;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_2;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_3;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_4;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_5;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_6;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_7;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_8;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_9;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_10;
ALTER DATABASE Demo ADD FILEGROUP fg_GUID_11;
-- 下面為這些檔組添加檔來進行物理的資料存儲
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_1' , FILENAME = 'C:/file1.NDF' ) TO FILEGROUP fg_GUID_1;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_2' , FILENAME = 'C:/file2.NDF' ) TO FILEGROUP fg_GUID_2;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_3' , FILENAME = 'C:/file3.NDF' ) TO FILEGROUP fg_GUID_3;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_4' , FILENAME = 'C:/file4.NDF' ) TO FILEGROUP fg_GUID_4;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_5' , FILENAME = 'C:/file5.NDF' ) TO FILEGROUP fg_GUID_5;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_6' , FILENAME = 'C:/file6.NDF' ) TO FILEGROUP fg_GUID_6;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_7' , FILENAME = 'C:/file7.NDF' ) TO FILEGROUP fg_GUID_7;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_8' , FILENAME = 'C:/file8.NDF' ) TO FILEGROUP fg_GUID_8;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_9' , FILENAME = 'C:/file9.NDF' ) TO FILEGROUP fg_GUID_9;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_10' , FILENAME = 'C:/file10.NDF' ) TO FILEGROUP fg_GUID_10;
ALTER DATABASE Demo ADD FILE ( NAME = 'f_GUID_11' , FILENAME = 'C:/file11.NDF' ) TO FILEGROUP fg_GUID_11;
go
USE Demo
go
-- 自定義分區的方法
/*
輔助函數
功能 :16 進制轉10進制
*/
IF OBJECT_ID ( 'F_Bigint10' , 'fn' ) IS NOT NULL
DROP FUNCTION F_Bigint10
go
create function F_Bigint10( @s nvarchar ( 50))
returns int
as
begin
declare @i BIGINT , @s2 nvarchar ( 2), @num BIGINT
select @i= len ( @s), @num= 0
while @i> 0
select @s2= substring ( reverse ( @s), @i, 1),
@num= power ( 16, @i- 1)*( charindex ( @s2, '0123456789ABCDEF' )- 1)+ @num,
@i= @i- 1
return @num
end
GO
IF OBJECT_ID ( 'F_Bigint16' , 'fn' ) IS NOT NULL
DROP FUNCTION F_Bigint16
go
/*
輔助函數
功能 :10 進制轉16進制
*/
create function F_Bigint16( @num BIGINT )
returns nvarchar ( 50)
as
begin
if @num= 0
return '0'
declare @s nvarchar ( 50)
set @s= ''
while @num> 0
select @s= substring ( '0123456789ABCDEF' , @num% 16+ 1, 1)+ @s, @num= @num/ 16
return @s
end
GO
-- 判斷存在對象時刪除
IF OBJECT_ID ( 'GUID' , 'U' )IS NOT NULL
DROP TABLE GUID
go
IF EXISTS( SELECT * FROM sys.partition_schemes WHERE name = 'sch_GUID' )
DROP PARTITION SCHEME sch_GUID
go
-- 分區函數（成立日期 GUID ）
IF EXISTS( SELECT 1 FROM sys.partition_functions WHERE name = N'fn_GUID' )
DROP PARTITION FUNCTION [fn_GUID]
go
CREATE PARTITION FUNCTION [fn_GUID]( [uniqueidentifier]) AS RANGE LEFT FOR VALUES
( '00000000-0000-0000-0000-174600000000'
, '00000000-0000-0000-0000-2E8B00000000'
, '00000000-0000-0000-0000-45D100000000'
, '00000000-0000-0000-0000-5D1700000000'
, '00000000-0000-0000-0000-745D00000000'
, '00000000-0000-0000-0000-8BA200000000'
, '00000000-0000-0000-0000-A2E800000000'
, '00000000-0000-0000-0000-BA2E00000000'
, '00000000-0000-0000-0000-D17400000000'
, '00000000-0000-0000-0000-E8B900000000'
)
go
-------------------------------------------------------
-- 創建分區架構
-------------------------------------------------------
CREATE PARTITION SCHEME sch_GUID
AS PARTITION fn_GUID TO (
fg_GUID_1,
fg_GUID_2,
fg_GUID_3,
fg_GUID_4,
fg_GUID_5,
fg_GUID_6,
fg_GUID_7,
fg_GUID_8,
fg_GUID_9,
fg_GUID_10,
fg_GUID_11
)
GO
-- 創建分區表
CREATE TABLE GUID
(
ID UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT PK_GUID PRIMARY KEY ,
Date DATETIME NOT NULL DEFAULT ( GETDATE ())
) ON sch_GUID( ID)
GO
INSERT GUID ( ID) VALUES ( NEWID ())
INSERT GUID ( ID) VALUES ( NEWID ())
INSERT GUID ( ID) VALUES ( NEWID ())
INSERT GUID ( ID) VALUES ( NEWID ())
--TRUNCATE TABLE GUID
go
-- 查看數據所在分區情況
SELECT *, $PARTITION . fn_GUID( ID) AS 據在分區 FROM GUID
/*
ID Date 據在分區
241AA8E2-0597-4447-BB53-0EFA71D27123 2010-12-28 10:43:17.250 1
D403D6AD-75A0-4E09-8C0D-23833D9281F2 2010-12-28 10:43:17.247 2
ED2A8127-FE33-41B1-86F6-739AC01E10E1 2010-12-28 10:43:17.250 5
9618232E-BB14-4E5E-96F7-F0DAFCF049BC 2010-12-28 10:43:17.250 11
*/
-- 統計一下各分區數據分佈情況
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID ( 'GUID' )
/*
partition_id object_id index_id partition_number hobt_id rows
72057594038321152 2073058421 1 1 72057594038321152 1
72057594038386688 2073058421 1 2 72057594038386688 1
72057594038452224 2073058421 1 3 72057594038452224 0
72057594038517760 2073058421 1 4 72057594038517760 0
72057594038583296 2073058421 1 5 72057594038583296 1
72057594038648832 2073058421 1 6 72057594038648832 0
72057594038714368 2073058421 1 7 72057594038714368 0
72057594038779904 2073058421 1 8 72057594038779904 0
72057594038845440 2073058421 1 9 72057594038845440 0
72057594038910976 2073058421 1 10 72057594038910976 0
72057594038976512 2073058421 1 11 72057594038976512 1
*/
TRUNCATE TABLE GUID -- 清空分區表
-- 查看以上 GUID 分區計算規則
INSERT GUID ( ID)
SELECT CAST ( '00000000-0000-0000-0000-174600000000' AS UNIQUEIDENTIFIER ) AS ID UNION ALL
SELECT '00000000-0000-0000-0000-2E8B00000000' UNION ALL
SELECT '00000000-0000-0000-0000-45D100000000' UNION ALL
SELECT '00000000-0000-0000-0000-5D1700000000' UNION ALL
SELECT '00000000-0000-0000-0000-745D00000000' UNION ALL
SELECT '00000000-0000-0000-0000-8BA200000000' UNION ALL
SELECT '00000000-0000-0000-0000-A2E800000000' UNION ALL
SELECT '00000000-0000-0000-0000-BA2E00000000' UNION ALL
SELECT '00000000-0000-0000-0000-D17400000000' UNION ALL
SELECT '00000000-0000-0000-0000-E8B900000000'
ORDER BY ID
SELECT
SUBSTRING ( RTRIM ( ID), 25, 4) AS [16 進制 ],
dbo. F_Bigint10( SUBSTRING ( RTRIM ( ID), 25, 4)) AS [10 進制 ],
SUBSTRING ( RTRIM ( ID), 25, 4) AS 分區段字符 ,
ID
FROM GUID
/*
16 進制 10 進制 分區段字符 ID
1746 5958 1746 00000000-0000-0000-0000-174600000000
2E8B 11915 2E8B 00000000-0000-0000-0000-2E8B00000000
45D1 17873 45D1 00000000-0000-0000-0000-45D100000000
5D17 23831 5D17 00000000-0000-0000-0000-5D1700000000
745D 29789 745D 00000000-0000-0000-0000-745D00000000
8BA2 35746 8BA2 00000000-0000-0000-0000-8BA200000000
A2E8 41704 A2E8 00000000-0000-0000-0000-A2E800000000
BA2E 47662 BA2E 00000000-0000-0000-0000-BA2E00000000
D174 53620 D174 00000000-0000-0000-0000-D17400000000
E8B9 59577 E8B9 00000000-0000-0000-0000-E8B900000000
*/
SELECT dbo. F_Bigint10( 'FFFF' ) AS 前段最大值 , dbo. F_Bigint10( 'FFFF' )/ 11 AS 分區範圍 --11 個分區
/*
前段最大值 分區範圍
65535 5957
*/
