SQL Server 2005对海量数据的处理
sql server2005 分区表分为:按数据行分 和按数据列分。分区后能实现数据存放在不同的文件上。是非常不错的大数据量的解决方案
建立 sql server 2005 分区表的步骤如下:
1:创建文件组
ALTER DATABASE shuxx
ADD FILE
(NAME = N'2007Q1',
FILENAME = N'C:\AdventureWorks\2007Q1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2007Q1]
ALTER DATABASE shuxx
ADD FILE
(NAME = N'2007Q2',
FILENAME = N'C:\AdventureWorks\2007Q2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2007Q2]
2:创建分区函数
create PARTITION FUNCTION OrderDateRangePFN(varchar(10))
AS
RANGE LEFT FOR VALUES ('1',
'2')
3:创建分区方案
CREATE PARTITION SCHEME OrderDatePScheme
4:创建分区表
CREATE TABLE [dbo].[OrdersRange]
(
[user_name] [varchar](10) not null,
[user_code] [varchar](10) not null
)
ON OrderDatePScheme (user_code)
GO
AS
PARTITION OrderDateRangePFN
TO ([2007Q1], [2007Q2], [PRIMARY])
5:查看数据分区情况
SELECT $PARTITION.OrderDateRangePFN (A.user_code) as partition_Number
,min(a.user_code) as [min_user_code],
max(a.user_code) as [max_user_code]
FROM dbo.OrdersRange as A
group by $partition.OrderDateRangePFN(A.user_code)