Data Partitioning in SQL Server 2000

There are two types of data partitioning: horizontal partitioning and vertical partitioning. In Horizontal Partitioning, a table is split horizontally with a subset of rows to form another similar table with the same number of columns. The structure of the table will remain the same. In Vertical Partitioning, a table with a large number of columns or very large columns is split into multiple partitions, each with the same number of rows but less number of columns. 

Here I am discussing the Horizontal Partitioning. We have a large table ORDERLINES with columns such as ORDER_NO, LINE_NO, CROSS_REF, USED_BUT_NOT_PRICED, LOST, and ORIG_COMM. Let us first partition ORDERLINES into four different tables. The data is divided with the following criteria:


1.       ORDER_NO <= ‘1020000’
2.       ORDER_NO BETWEEN ‘1020000’ AND ‘1040000’
3.       ORDER_NO BETWEEN ‘1040000’ AND ‘1060000’
4.       ORDER_NO >= ‘1060000’ 

Given below are the steps involved in this process: 

I. Create 4 tables with the same structure as that of ORDERLINES 

1. CREATE TABLE [dbo].[ORDERLINES01] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

2. CREATE TABLE [dbo].[ORDERLINES02] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

3. CREATE TABLE [dbo].[ORDERLINES03] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

4. CREATE TABLE [dbo].[ORDERLINES04] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

II. Insert data into these four tables  


1. INSERT INTO ORDERLINES01 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [SED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO <= '1020000')
     GO
     UPDATE ORDERLINES01
     SET OLID = '1'
     GO

2. INSERT INTO ORDERLINES02 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO BETWEEN '1020000' AND '1040000')
 GO
 UPDATE ORDERLINES02
 SET OLID = '2'
 GO

3. INSERT INTO ORDERLINES03 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO BETWEEN '1040000' AND '1060000')
    GO
    UPDATE ORDERLINES03
    SET OLID = '3'
    GO

4. INSERT INTO ORDERLINES04 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST],  ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER ORDER_NO >= '1060000')
    GO
    UPDATE ORDERLINES04
    SET OLID = '4'
    GO


 III. Build Constraints

For the Query Processor to know that each partitioned table contains only a certain type of data, we need to build CHECK constraints on each table on the ID that we have partitioned. Hence we will build CHECK constraints on OLID.                        

 1. ALTER TABLE ORDERLINES01
   ADD CONSTRAINT CK_OL1 CHECK (OLID=1)
  2. ALTER TABLE ORDERLINES02
   ADD CONSTRAINT CK_OL2 CHECK (OLID=2)
  3. ALTER TABLE ORDERLINES03
   ADD CONSTRAINT CK_OL3 CHECK (OLID=3)
  4. ALTER TABLE ORDERLINES04
   ADD CONSTRAINT CK_OL4 CHECK (OLID=4)

IV. Build the View

CREATE VIEW ORDERLINES
   AS
    SELECT * FROM ORDERLINES01
   UNION ALL
    SELECT * FROM ORDERLINES02
   UNION ALL
    SELECT * FROM ORDERLINES03
   UNION ALL
    SELECT * FROM ORDERLINES04

Conclusion 

The View shown above is the same as the original ORDERLINES table. But a query like

SELECT * FROM ORDERLINES WHERE OLID=2,

will not seek all the ORDERLINES records. It will limit the seek to ORDERLINES02 table only.

Advertisements

3 thoughts on “Data Partitioning in SQL Server 2000

  1. Coenraad

    What about the insertion of NEW order records into the original ORDERLINES table from external processes / applications?

    Also, what about any indices and database triggers that are on the orginal ORDERLINES table?

    Reply
  2. Code

    Thanks for your post. I want you to ask two questions:

    1. Is there strong reason do not use all columns from original table (4 of 6 used)?
    2. Can I update data with created view?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s