Creating Partitioned Table in SQL Server 2005
SQL Server 2005 supports partitioned table to enable database developer to separate tables horizontally to different file groups. This post shows how to create partitioned tables in SQL Server 2005.
Click “Start”, click “All Programs”, move your mouse on “SQL Server 2005” and then click “SQL Server Management Studio” in the context menu.
Connect to SQL Server 2005 with a Windows account or SQL Server account with adequate privileges. (It’s OK for you not to connect to the server at this time.. However, you need to connect to the server with an appropriate account when you want to execute your T-SQL statements.)
If you haven’t created file groups for storing database files, you need to create them at this time. Let me use the built-in AdventureWorks database as an example. The following codes create 3 file groups.
Use AdventureWorks
GO
ALTER DATABASE AdventureWorks ADD FILEGROUP fg1
ALTER DATABASE AdventureWorks ADD FILEGROUP fg2
ALTER DATABASE AdventureWorks ADD FILEGROUP fg3
GO
Then let me add three files. Each to one file group.
ALTER DATABASE AdventureWorks
ADD FILE
( Name = data1,
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\data1.mdf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg1
ALTER DATABASE AdventureWorks
ADD FILE
( Name = data2,
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\data2.mdf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg2
ALTER DATABASE AdventureWorks
ADD FILE
( Name = data3,
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\data3.mdf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg3
Thirdly, let me create a partition function.
CREATE PARTITION FUNCTION namePF(nvarchar(20))
AS RANGE LEFT FOR VALUES ('H', 'P')
GO
This partition function named “namePF” means to separate the names from left. Actually, there will be 3 separate groups, from infinity to H, from H to P and from P to infinity.
Let me create a partition scheme to define these groups to actual file groups.
CREATE PARTITION SCHEME namePS
AS PARTITION namePF TO (fg1, fg2, fg3)
GO
Lastly, let me create a partition table called CustomerNameTable with the partition function and partition scheme I just created.
CREATE TABLE CustomerNameTable
(CustID int identity, Name nvarchar(20))
ON namePS (Name)
GO
We’ve done. Now let me test the partitioned table I created. Let me insert some information to the table, with different content in the column called “Name”. I use “Alan N Yan” as the example from infinity (actually A) to H, “Nuo Yan” as the example from H to P, and “Queen Betty” as the example from P to infinity (actually Z).
INSERT CustomerNameTable VALUES ('Alan N Yan')
INSERT CustomerNameTable VALUES ('Nuo Yan')
INSERT CustomerNameTable VALUES ('Queen Betty')
Then let me retrieve the partition information and see the results.
SELECT Name, $partition.namePF(Name) Partition
FROM CustomerNameTable
Result:
| Name |
Partition |
| -------------------- |
----------- |
| Alan N Yan |
1 |
| Nuo Yan |
2 |
| Queen Betty |
3 |
(3 row(s) affected)
Let’s see, “Alan N Yan” has automatically been placed into the first partition, “Nuo Yan” has automatically been placed into the second partition, and “Queen Betty” has automatically been placed into the third partition.