Share via

SQL Server: Table Partitioning in SQL Server 2005

  • Article

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"));
db.Create();

 

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

 

PartitionScheme ps = new PartitionScheme(db, "MyPartitionScheme");
ps.PartitionFunction = "MyPartitionFunction";
ps.FileGroups.Add("PRIMARY");
ps.FileGroups.Add("SECONDARY");
ps.Create();

 

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"));
t.Create();

 

// 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())
    {
        Console.WriteLine(s);
    }
    Console.WriteLine("GO");
}

 

 

Comments

  • Anonymous
    August 17, 2006
    Thanks !!
  • Anonymous
    January 13, 2008
    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!