关于分区表及分区视图的用法

分区表的特点就是含有约束，且约束必须为主键之一。

分区视图 就是联接所有分区表，视图中的分区表结构必须相同。

更新分区表中的数据时 直接操作分区视图，如新增，修改，删除，都直可以针对分区视图进行操作。

分区视图在创建时会自动创建索引，会根据约束关系直接关联到具体分区表，具体见附件

if exists (select * from sysobjects where name = 'at_Card_200905' and type='U')
begin
 drop table at_Card_200905
end
go
CREATE TABLE [dbo].[at_Card_200905] 
(
[CardMoth] nvarchar(6) NOT NULL,
[EmployeeID] nvarchar(40) NOT NULL,
[CardTime] datetime NOT NULL
PRIMARY KEY  CLUSTERED
 (
  [CardMoth],
  [EmployeeID],
  [CardTime]
 )  ON [PRIMARY] ,
CHECK ([CardMoth] = '200905' )  
)
GO
if exists (select * from sysobjects where name = 'at_Card_200906' and type='U')
begin
 drop table at_Card_200906
end
go
CREATE TABLE [dbo].[at_Card_200906] 
(
[CardMoth] nvarchar(6) NOT NULL,
[EmployeeID] nvarchar(40) NOT NULL,
[CardTime] datetime NOT NULL
PRIMARY KEY  CLUSTERED
 (
  [CardMoth],
  [EmployeeID],
  [CardTime]
 )  ON [PRIMARY] ,
CHECK ([CardMoth] = '200906' )  
)
GO
if exists (select * from sysobjects where name = 'at_Card_200907' and type='U')
begin
 drop table at_Card_200907
end
go
CREATE TABLE [dbo].[at_Card_200907] 
(
[CardMoth] nvarchar(6) NOT NULL,
[EmployeeID] nvarchar(40) NOT NULL,
[CardTime] datetime NOT NULL
PRIMARY KEY  CLUSTERED
 (
  [CardMoth],
  [EmployeeID],
  [CardTime]
 )  ON [PRIMARY] ,
CHECK ([CardMoth] = '200907' ) 
)
GO
if exists (select * from sysobjects where name = 'av_Card' and type='v')
begin
 drop View av_Card
end
go
create view av_Card
as
select * from at_Card_200905
union all
select * from at_Card_200906
union all
select * from at_Card_200907
Go

Insert into av_Card (CardMoth,EmployeeID,CardTime)
values('200905','U001',getdate());
Insert into av_Card (CardMoth,EmployeeID,CardTime)
values('200906','U002',getdate());
Insert into av_Card (CardMoth,EmployeeID,CardTime)
values('200906','U003',getdate());
Go

Select * from av_Card;