采用GUID分區方法

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <mce:style><!-- st1/:*{behavior:url(#ieooui) } --> <!-- [endif]--><!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

/*

版本

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

*/