CREATE PARTITION FUNCTION myRange (varchar(20)) AS RANGE RIGHT FOR VALUES ('B0001', 'C0001', 'D0001'); GO CREATE PARTITION SCHEME myRange AS PARTITION myRange ALL TO ( [PRIMARY] );CREATE TABLE Test (col1 INT IDENTITY(1,1), col2 varchar(20)) ON myRange (col2) ; GOINSERT INTO Test (col2) VALUES('A001')INSERT INTO Test (col2) VALUES('B001') INSERT INTO Test (col2) VALUES('B002')INSERT INTO Test (col2) VALUES('C001') INSERT INTO Test (col2) VALUES('C002') INSERT INTO Test (col2) VALUES('C003')INSERT INTO Test (col2) VALUES('D001') INSERT INTO Test (col2) VALUES('D002') INSERT INTO Test (col2) VALUES('D003') INSERT INTO Test (col2) VALUES('D004') GO--查看各分区记录 SELECT $partition.myRange(o.col2)AS[Partition Number] ,min(o.col2) AS [Min ID] ,max(o.col2) AS [Max ID] ,count(*) AS [Rows In Partition] FROM dbo.Test AS o GROUP BY $partition.myRange(o.col2) ORDER BY [Partition Number]
谢了,今天沟通了下需求,有可能包含的不止是guid 还有可能是其他的唯一的什么东西,最后我的方案如下 CREATE PARTITION FUNCTION PF_UserPFN(VARCHAR(50)) AS RANGE right FOR VALUES ( '0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');
CREATE PARTITION FUNCTION myRange (varchar(20))
AS RANGE RIGHT FOR VALUES ('B0001', 'C0001', 'D0001');
GO
CREATE PARTITION SCHEME myRange
AS PARTITION myRange
ALL TO ( [PRIMARY] );CREATE TABLE Test (col1 INT IDENTITY(1,1), col2 varchar(20))
ON myRange (col2) ;
GOINSERT INTO Test (col2) VALUES('A001')INSERT INTO Test (col2) VALUES('B001')
INSERT INTO Test (col2) VALUES('B002')INSERT INTO Test (col2) VALUES('C001')
INSERT INTO Test (col2) VALUES('C002')
INSERT INTO Test (col2) VALUES('C003')INSERT INTO Test (col2) VALUES('D001')
INSERT INTO Test (col2) VALUES('D002')
INSERT INTO Test (col2) VALUES('D003')
INSERT INTO Test (col2) VALUES('D004')
GO--查看各分区记录
SELECT $partition.myRange(o.col2)AS[Partition Number]
,min(o.col2) AS [Min ID]
,max(o.col2) AS [Max ID]
,count(*) AS [Rows In Partition]
FROM dbo.Test AS o
GROUP BY $partition.myRange(o.col2)
ORDER BY [Partition Number]
CREATE PARTITION FUNCTION PF_UserPFN(VARCHAR(50)) AS RANGE right FOR VALUES (
'0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');