Convert a nonpartitioned table to a partitioned table

1. Create a database OYCO
USE MASTER
CREATE DATABASE OYCO
ON PRIMARY
(NAME='OYCO_DAT',FILENAME='C:\DATA\OYCO_DAT.mdf'),
FILEGROUP FG1
(NAME='FG1_DAT',FILENAME='C:\DATA\FG1_DAT.ndf'),
FILEGROUP FG2
(NAME='FG2_DAT',FILENAME='C:\DATA\FG2_DAT.ndf'),
FILEGROUP FG3
(NAME='FG3_DAT',FILENAME='C:\DATA\FG3_DAT.ndf'),
FILEGROUP FG4
(NAME='FG4_DAT',FILENAME='C:\DATA\FG4_DAT.ndf')
LOG ON
(NAME='OYCO_LOG',FILENAME='C:\DATA\OYCO_LOG.ldf');
GO


2. Create a Partition function and scheme
USE OYCO
CREATE PARTITION FUNCTION pf(INT)
AS RANGE LEFT FOR VALUES(100,500,1000)
GO
CREATE PARTITION SCHEME ps AS PARTITION pf
TO (FG1,FG2,FG3,FG4)
GO

3. Create a table without partition

USE OYCO;
CREATE TABLE [Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[AddressLine1] [nvarchar](60) NOT NULL,
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL
) ON FG1

4. Import data from AdventureWorks.Person.Address into Oyco.dbo.Address table

In command prompt:
Export data--

bcp "SELECT AddressID,AddressLine1,City,PostalCode FROM AdventureWorks.Person.Address" queryout c:\myAddress.dat -T -c -t "&"

Import data--
bcp OYCO.dbo.Address in C:\myAddress.dat -T -c -t "&"

5. Re-create the unique clustered index

CREATE UNIQUE CLUSTERED INDEX inx_clustered ON dbo.Address(AddressID)
WITH DROP_EXISTING
ON ps(AddressID);

SELECT * FROM sys.partitions
WHERE object_id=OBJECT_ID('dbo.address')

-----------

Another method needs more steps, as described below:

• CREATE PARTITION FUNCTION pf(INT) AS RANGE LEFT FOR VALUES()
• CREATE PARTITION SCHEME ps AS PARTITION pf TO (PRIMARY)

The above statements create a partition scheme with only one partition.

• Create a temporary table based on the partition scheme.
• ALTER TABLE source_nonpartitionedtable SWITCH TO temporary_partitionedTable Partition 1
  

• ALTER PARTITION SCHEME ps NEXT USED [Filegroup]
• ALTER PARTITION FUNCTION pf SPLIT RANGE (value)

The temporary partitioned table will have more partitions.

• DROP the original table
• EXEC sp_rename 'TemporaryTableName' 'OriginalTableName'