Partitioned View
--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum)
)
GO
INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO
--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO
--Look at execution plan for this query
SELECT *
FROM sales_national
WHERE region = 'south'
