分区视图试验代码
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[customer_s]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[customer_s]
GO
CREATE TABLE [dbo].[customer_s] (
[customer_id] INT,
[account_num] [float] NULL ,
[lname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_customer_s] PRIMARY KEY CLUSTERED (customer_id),
CONSTRAINT [CK_customer_s] CHECK ([customer_id] <= 1000)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[customer_m]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[customer_m]
GO
CREATE TABLE [dbo].[customer_m] (
[customer_id] INT,
[account_num] [float] NULL ,
[lname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_customer_m] PRIMARY KEY CLUSTERED (customer_id),
CONSTRAINT [CK_customer_m] CHECK ([customer_id] > 1000)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_customer]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_customer]
GO
CREATE VIEW v_customer
AS
SELECT * FROM customer_s
UNION ALL
SELECT * FROM customer_m
GO
INSERT INTO v_customer(customer_id,account_num,lname,fname)
SELECT customer_id,account_num,lname,fname FROM customer
drop table [dbo].[customer_s]
GO
CREATE TABLE [dbo].[customer_s] (
[customer_id] INT,
[account_num] [float] NULL ,
[lname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_customer_s] PRIMARY KEY CLUSTERED (customer_id),
CONSTRAINT [CK_customer_s] CHECK ([customer_id] <= 1000)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[customer_m]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[customer_m]
GO
CREATE TABLE [dbo].[customer_m] (
[customer_id] INT,
[account_num] [float] NULL ,
[lname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[fname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_customer_m] PRIMARY KEY CLUSTERED (customer_id),
CONSTRAINT [CK_customer_m] CHECK ([customer_id] > 1000)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_customer]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_customer]
GO
CREATE VIEW v_customer
AS
SELECT * FROM customer_s
UNION ALL
SELECT * FROM customer_m
GO
INSERT INTO v_customer(customer_id,account_num,lname,fname)
SELECT customer_id,account_num,lname,fname FROM customer