采用GUID分區方法

SQL Server技巧 专栏收录该内容
26 篇文章 1 订阅

/*

版本

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

*/

 

 

  • 1
    点赞
  • 1
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

评论 1 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

中国风

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值