Share via

SMO Sample: Partitions

  • Article

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

  • Anonymous
    September 14, 2007
    this is great, but how do you just change the location of the Database mdl/ldf file?