SQL Server: Table Partitioning in SQL Server 2005

Table and Index partitioning is one if the new SQL Server 2005 features that willl improve life for the DBA and application developer quite a bit. It allows Indexes and Tables to be partitioned across multiple file groups. Partitioned tables and indexes, are fully manageable with SMO. Here is a quick sample to get you started, with not too much explanation as speaks for itself, mostly. Let me know if you have questions!

Server svr = new Server();


Database db = new Database(svr, "MyDatabase");
db.FileGroups.Add(new FileGroup(db, "PRIMARY"));
db.FileGroups.Add(new FileGroup(db, "SECONDARY"));
db.FileGroups[0].Files.Add(new DataFile(db.FileGroups[0], "datafile1", @"c:\db1.mdf"));
db.FileGroups[1].Files.Add(new DataFile(db.FileGroups[1], "datafile2", @"c:\db2.mdf"));


PartitionFunction pf = new PartitionFunction(db, "MyPartitionFunction");
pf.PartitionFunctionParameters.Add(new PartitionFunctionParameter(pf, DataType.Int));
pf.RangeType = RangeType.Left;
pf.RangeValues = new object[] { 5000 };


PartitionScheme ps = new PartitionScheme(db, "MyPartitionScheme");
ps.PartitionFunction = "MyPartitionFunction";


Table t = new Table(db, "MyTable");
t.Columns.Add(new Column(t, "c1", DataType.Int));
t.Columns.Add(new Column(t, "c2", DataType.VarChar(100)));
t.PartitionScheme = "MyPartitionScheme";
t.PartitionSchemeParameters.Add(new PartitionSchemeParameter(t, "c1"));


// Script out the objects (for educational purposes; optional)
SqlSmoObject[] objs = new SqlSmoObject[4];
objs[0] = db;
objs[1] = pf;
objs[2] = ps;
objs[3] = t;


for (int i = 0; i < 4; i++)
    Console.WriteLine("-- " + objs[i].ToString());
    foreach (string s in ((IScriptable)(objs[i])).Script())



Comments (2)

  1. Turkay Kaynak says:

    Thanks !!

  2. Luis Simoes says:

    How do i detect using SMO the partition number where a certain key is on? Also how do i get the type of data used in the partitioning?

    Best Regards and Congrats for the great job here!

Skip to main content