我使用三台SQL Server 2012 搭建分布式数据库,将一年的1.4亿条数据大致均匀存储在这三台Server中,每台Server 存储4个月的数据,Physical Server的配置基本相同,内存16G,双核 CPU 3.6GHz。
  1,创建分区视图,Linked Server的Alias是db2 和 db3,Catalog 是 tdw(test data warehouse)。
CREATE TABLE [dbo].[Commits]
(
[CommitID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AuthorID] [bigint] NOT NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[CreatedDateKey] [int] NOT NULL,
CONSTRAINT [PK__Commits_CommitID] PRIMARY KEY CLUSTERED
(
[CommitID] ASC,
[CreatedDateKey] ASC
)
) ON [PRIMARY]
GO
CREATE view [dbo].[view_commits]
as
select [CommitID]
,[AuthorID]
,[CreatedDate]
,[CreatedDateKey]
from dbo.commits c with(nolock)
where c.[CreatedDateKey] between 20150900 and 20160000
union ALL
select [CommitID]
,[AuthorID]
,[CreatedDate]
,[CreatedDateKey]
from db3.tdw.dbo.commits c with(nolock)
where c.[CreatedDateKey] between 20150000 and 20150500
union ALL
select [CommitID]
,[AuthorID]
,[CreatedDate]
,[CreatedDateKey]
from db2.tdw.dbo.commits c with(nolock)
where c.[CreatedDateKey] between 20150500 and 20150900
WITH check OPTION;
GO
  2,查询性能测试
  Test1,使用Basic Table测试,cost:79s
select count(0)
from dbo.commits_total c  with(nolock)
where day(c.[CreatedDate])=1

  Test2,使用分区视图测试,cost=134s,跟Test1有明显的差距。
select count(0)
from dbo.view_commits c  with(nolock)
where day(c.[CreatedDate])=1