SMO Sample: Partitions


Purpose of sample: create a partition function and partition scheme on an existing database


Server svr = new Server(instance);


if (svr.Databases.Contains("smotestdb")) // change the name if needed
{
   svr.Databases[
"smotestdb"].Drop();
}


Database db = new Database(svr, "smotestdb");
db.Create();


db.FileGroups.Add(new FileGroup(db, "PART1_FG"));
db.FileGroups.Add(
new FileGroup(db, "PART2_FG"));
db.FileGroups[0].Files.Add(
new DataFile(db.FileGroups[0], "datafile1", @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_1.mdf"));
db.FileGroups[1].Files.Add(
new DataFile(db.FileGroups[1], "datafile2", @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_2.mdf"));
db.Alter();


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(
"PART1_FG");
ps.FileGroups.Add(
"PART2_FG");
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();

Disclaimer: this sample doesn't handle exceptions and may not function as expected. Use at own risk. It is good practice to test an application before using it in production.

Comments (1)

  1. Ben says:

    this is great, but how do you just change the location of the Database mdl/ldf file?

Skip to main content