分区表的特点就是含有约束,且约束必须为主键之一。
分区视图 就是联接所有分区表,视图中的分区表结构必须相同。
更新分区表中的数据时 直接操作分区视图,如新增,修改,删除,都直可以针对分区视图进行操作。
分区视图在创建时会自动创建索引,会根据约束关系直接关联到具体分区表,具体见附件
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
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;