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) ;
GO

INSERT 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]