SQL - Structured Query Language

Data Partitioned Technique - VIEW

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column.
A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows.
The following queries are used to create Supplier_North, Supplier_East, Supplier_West and Supplier_South tables and insert some sample records.

-- Example 62 --

USE EBusiness

---------------------------

-- STEP 01 Create Tables --

---------------------------

CREATE TABLE Supplier_North

(

Supplier_ID int PRIMARY KEY CHECK (Supplier_ID BETWEEN 1 AND 150),

Supplier_Name varchar(50) NOT NULL

)

CREATE TABLE Supplier_East

(

Supplier_ID int PRIMARY KEY CHECK (Supplier_ID BETWEEN 151 AND 300),

Supplier_Name varchar(50) NOT NULL

)

CREATE TABLE Supplier_West

(

Supplier_ID int PRIMARY KEY CHECK (Supplier_ID BETWEEN 301 AND 450),

Supplier_Name varchar(50) NOT NULL

)

CREATE TABLE Supplier_South

(

Supplier_ID int PRIMARY KEY CHECK (Supplier_ID BETWEEN 451 AND 600),

Supplier_Name varchar(50) NOT NULL

)

-----------------------------------

-- STEP 02 Insert sample records --

-----------------------------------

INSERT INTO Supplier_North VALUES (1,'California Corp')

INSERT INTO Supplier_North VALUES (5,'Brazilia Ltd')

INSERT INTO Supplier_East VALUES (231,'Far East Ltd')

INSERT INTO Supplier_East VALUES (280,'NZ Ltd')

INSERT INTO Supplier_West VALUES (321,'Euro Group')

INSERT INTO Supplier_West VALUES (442,'UK Archip')

INSERT INTO Supplier_South VALUES (475,'India')

INSERT INTO Supplier_South VALUES (521,'Afrique')

---------------------------------------------------------------------------------

-- STEP 03 Create Combines All Supplier Tables by using UNION ALL SET Operator --

---------------------------------------------------------------------------------

CREATE VIEW ListOfSuppliers

SELECT * FROM Supplier_North

UNION ALL

SELECT * FROM Supplier_East

UNION ALL

SELECT * FROM Supplier_West

UNION ALL

SELECT * FROM Supplier_South

----------------------------------------------

-- STEP 04 Display List of Supplier Records --

----------------------------------------------

SELECT * FROM ListOfSuppliers

Query Output Screen

* * * * *

Your Comment To AUTHOR