我用sqlsever2008 做个了大表分区。 发现08好快,傻瓜式。
他自动生成的脚本如下:
CREATE PARTITION FUNCTION [ordersfunction](datetime) AS RANGE LEFT FOR VALUES (N'2008-01-01T00:00:00', N'2009-01-01T00:00:00', N'2010-01-01T00:00:00', N'2011-01-01T00:00:00')CREATE PARTITION SCHEME [orderscheme] AS PARTITION [ordersfunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])CREATE CLUSTERED INDEX [ClusteredIndex_on_orderscheme_634148060153437500] ON [dbo].[Orders]
(
[orderDate]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [orderscheme]([orderDate])DROP INDEX [ClusteredIndex_on_orderscheme_634148060153437500] ON [dbo].[Orders] WITH ( ONLINE = OFF )对比之前2005下累死累活的脚本
05下实现大表分区
-- 如何创建分区函数
-- 如何创建分区架构
-- 如何创建分区表
--=========================
alter database adventureWorks add filegroup [fg1]
go
alter database adventureWorks add filegroup [fg2]
go
alter database adventureWorks add filegroup [fg3]
go
alter database adventureWorks
add file
(name='fg1',
filename='c:\fg1.ndf',
size=5mb)
to filegroup [fg1]
go
alter database adventureWorks
add file
(name='fg2',
filename='d:\fg2.ndf',
size=5mb)
to filegroup [fg2]
go
alter database adventureWorks
add file
(name='fg3',
filename='e:\fg3.ndf',
size=5mb)
to filegroup [fg3]
go
use adventureWorks
go
Create partition function emailPF(nvarchar(50)) as range right for values ('G','N')--创建分区函数
go
Create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)--创建分区方案
go
Create table customermail (custid int, email nvarchar(50)) on emailPS(email)--创建分区表
Go
问题:
这里一个问题 , 请问08里里类似这样的'e:\fg1.ndf' 'e:\fg2.ndf' 'e:\fg3.ndf'到哪里去了?? (是否08 跟05 大表分布区有了本质的区别, 机制都不一样了??/)
他自动生成的脚本如下:
CREATE PARTITION FUNCTION [ordersfunction](datetime) AS RANGE LEFT FOR VALUES (N'2008-01-01T00:00:00', N'2009-01-01T00:00:00', N'2010-01-01T00:00:00', N'2011-01-01T00:00:00')CREATE PARTITION SCHEME [orderscheme] AS PARTITION [ordersfunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])CREATE CLUSTERED INDEX [ClusteredIndex_on_orderscheme_634148060153437500] ON [dbo].[Orders]
(
[orderDate]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [orderscheme]([orderDate])DROP INDEX [ClusteredIndex_on_orderscheme_634148060153437500] ON [dbo].[Orders] WITH ( ONLINE = OFF )对比之前2005下累死累活的脚本
05下实现大表分区
-- 如何创建分区函数
-- 如何创建分区架构
-- 如何创建分区表
--=========================
alter database adventureWorks add filegroup [fg1]
go
alter database adventureWorks add filegroup [fg2]
go
alter database adventureWorks add filegroup [fg3]
go
alter database adventureWorks
add file
(name='fg1',
filename='c:\fg1.ndf',
size=5mb)
to filegroup [fg1]
go
alter database adventureWorks
add file
(name='fg2',
filename='d:\fg2.ndf',
size=5mb)
to filegroup [fg2]
go
alter database adventureWorks
add file
(name='fg3',
filename='e:\fg3.ndf',
size=5mb)
to filegroup [fg3]
go
use adventureWorks
go
Create partition function emailPF(nvarchar(50)) as range right for values ('G','N')--创建分区函数
go
Create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)--创建分区方案
go
Create table customermail (custid int, email nvarchar(50)) on emailPS(email)--创建分区表
Go
问题:
这里一个问题 , 请问08里里类似这样的'e:\fg1.ndf' 'e:\fg2.ndf' 'e:\fg3.ndf'到哪里去了?? (是否08 跟05 大表分布区有了本质的区别, 机制都不一样了??/)
select *,$partition.ordersfunction(orderDate) from Orders where id =1;
-------------用sqlsever2008 傻瓜式做的SELECT $partition.ordersfunction(o.orderDate) AS [Partition Number], max(o.orderDate) AS [Max], count(*) AS [Rows] FROM Orders AS o GROUP BY $partition.ordersfunction(o.orderDate)07年做过一段时间的etl工程师
发现sqlserver08的 大表分区很不够灵活。维护好麻烦。
加分区 减去分区 都必须重新做一次,几乎没有办法不重新做。老问题 (未解决)
08里里类似这样的'e:\fg1.ndf' 'e:\fg2.ndf' 'e:\fg3.ndf'到哪里去了?? (是否08 跟05 大表分布区有了本质的区别, 机制都不一样了??/)新问题
1 sqlserver 加减新分区 维护实在好麻烦,试用发现我在date上面不能随便加索引,否则导致分区函数 分区模式不能用?
2 到底好在哪里?? (试用感觉饿对于在线的大表 不是很好啦)