SQL Server Partitioned Tables

Why Partitions?

        Microsoft introduced table partitioning functionality from SQL Server 2005 onwards. Usually partitions are implemented on large tables. With the help of partitioning, tables or indexes are becomes easy to manage, because partitioning enables you to manage and access subsets of data quickly and efficiently. By using partitioning loading and deleting data only takes seconds instead of minutes or hours in case of non-partitioned tables. Select opration is also becomes more efficient as it target only the data that is required, instead of enitre data.

Steps to create Partitioned Table  

        Follow steps mentioned below to create a partitioned table in SQL Server. 

1. Create a partition function to specify how a table or index that is being sliced.
2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
3. Create a table or index using the partition scheme.

Example:

 -- Create Partition Function
CREATE PARTITION FUNCTION pfTestPartition (SMALLDATETIME)
AS RANGE LEFT FOR VALUES ('2010-02-01 23:59:00', '2010-02-02 23:59:00')

GO

-- Create Partition Schema

CREATE PARTITION SCHEME psTestPartition

AS PARTITION pfTestPartition ALL TO ([PRIMARY])

GO

-- Create Partitioned Table

CREATE TABLE TestPartition

( ID INT IDENTITY(1,1),

  EndTime SMALLDATETIME NOT NULL )

GO

ALTER TABLE TestPartition

ADD CONSTRAINT [pk_TestPartition]

PRIMARY KEY CLUSTERED

(

      [EndTime] ASC

)ON psTestPartition (EndTime)

GO