static void Main()
{
Server server = new Server(@".");
string dbName = @"SMOPartitionAndCompressionSampleDB";
Database db = new Database(server, dbName);
db.Create();
db.FileGroups.Add(new FileGroup(db, "TABLE_PART1_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART2_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART3_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART4_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART5_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART6_FG"));
string temp_dir = server.Information.MasterDBPath;
//db.FileGroups[0] is primary
db.FileGroups[1].Files.Add(new DataFile(db.FileGroups[1], "tbl_datafile1", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_1.mdf", temp_dir)));
db.FileGroups[2].Files.Add(new DataFile(db.FileGroups[2], "tbl_datafile2", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_2.mdf", temp_dir)));
db.FileGroups[3].Files.Add(new DataFile(db.FileGroups[3], "tbl_datafile3", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_3.mdf", temp_dir)));
db.FileGroups[4].Files.Add(new DataFile(db.FileGroups[4], "tbl_datafile4", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_4.mdf", temp_dir)));
db.FileGroups[5].Files.Add(new DataFile(db.FileGroups[5], "tbl_datafile5", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_5.mdf", temp_dir)));
db.FileGroups[6].Files.Add(new DataFile(db.FileGroups[6], "tbl_datafile6", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_6.mdf", temp_dir)));
db.Alter();
PartitionFunction tbl_pf = new PartitionFunction(db, "MyTablePartitionFunction");
tbl_pf.PartitionFunctionParameters.Add(new PartitionFunctionParameter(tbl_pf, DataType.Int));
tbl_pf.RangeType = RangeType.Left;
tbl_pf.RangeValues = new object[] { 5000, 10000, 15000, 20000, 25000, 30000 };
tbl_pf.Create();
PartitionScheme tbl_ps = new PartitionScheme(db, "MyTablePartitionScheme");
tbl_ps.PartitionFunction = "MyTablePartitionFunction";
tbl_ps.FileGroups.Add("PRIMARY");
tbl_ps.FileGroups.Add("TABLE_PART1_FG");
tbl_ps.FileGroups.Add("TABLE_PART2_FG");
tbl_ps.FileGroups.Add("TABLE_PART3_FG");
tbl_ps.FileGroups.Add("TABLE_PART4_FG");
tbl_ps.FileGroups.Add("TABLE_PART5_FG");
tbl_ps.FileGroups.Add("TABLE_PART6_FG");
tbl_ps.Create();
Table table = new Table(db, "MyTable");
table.Columns.Add(new Column(table, "col1", DataType.Int));
table.Columns.Add(new Column(table, "col2", DataType.Int));
table.PartitionScheme = "MyTablePartitionScheme";
table.PartitionSchemeParameters.Add(new PartitionSchemeParameter(table, "col1"));
//Add PhysicalPartition objects for the partitions on which you like to apply compression.
//Make Partition 5 as Page compressed
table.PhysicalPartitions.Add(new PhysicalPartition(table, 5, DataCompressionType.Page));
//Make Partition 6 and 7 Row compressed
table.PhysicalPartitions.Add(new PhysicalPartition(table, 6, DataCompressionType.Row));
table.PhysicalPartitions.Add(new PhysicalPartition(table, 7, DataCompressionType.Row));
table.Create();
}
static void Main()
{
Server server = new Server(@".");
string dbName = @"SMOPartitionAndCompressionSampleDB";
Database db = new Database(server, dbName);
db.Create();
db.FileGroups.Add(new FileGroup(db, "TABLE_PART1_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART2_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART3_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART4_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART5_FG"));
db.FileGroups.Add(new FileGroup(db, "TABLE_PART6_FG"));
string temp_dir = server.Information.MasterDBPath;
//db.FileGroups[0] is primary
db.FileGroups[1].Files.Add(new DataFile(db.FileGroups[1], "tbl_datafile1", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_1.mdf", temp_dir)));
db.FileGroups[2].Files.Add(new DataFile(db.FileGroups[2], "tbl_datafile2", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_2.mdf", temp_dir)));
db.FileGroups[3].Files.Add(new DataFile(db.FileGroups[3], "tbl_datafile3", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_3.mdf", temp_dir)));
db.FileGroups[4].Files.Add(new DataFile(db.FileGroups[4], "tbl_datafile4", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_4.mdf", temp_dir)));
db.FileGroups[5].Files.Add(new DataFile(db.FileGroups[5], "tbl_datafile5", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_5.mdf", temp_dir)));
db.FileGroups[6].Files.Add(new DataFile(db.FileGroups[6], "tbl_datafile6", string.Format(CultureInfo.InvariantCulture, "{0}\\PPSampledb_6.mdf", temp_dir)));
db.Alter();
PartitionFunction tbl_pf = new PartitionFunction(db, "MyTablePartitionFunction");
tbl_pf.PartitionFunctionParameters.Add(new PartitionFunctionParameter(tbl_pf, DataType.Int));
tbl_pf.RangeType = RangeType.Left;
tbl_pf.RangeValues = new object[] { 5000, 10000, 15000, 20000, 25000, 30000 };
tbl_pf.Create();
PartitionScheme tbl_ps = new PartitionScheme(db, "MyTablePartitionScheme");
tbl_ps.PartitionFunction = "MyTablePartitionFunction";
tbl_ps.FileGroups.Add("PRIMARY");
tbl_ps.FileGroups.Add("TABLE_PART1_FG");
tbl_ps.FileGroups.Add("TABLE_PART2_FG");
tbl_ps.FileGroups.Add("TABLE_PART3_FG");
tbl_ps.FileGroups.Add("TABLE_PART4_FG");
tbl_ps.FileGroups.Add("TABLE_PART5_FG");
tbl_ps.FileGroups.Add("TABLE_PART6_FG");
tbl_ps.Create();
Table table = new Table(db, "MyTable");
table.Columns.Add(new Column(table, "col1", DataType.Int));
table.Columns.Add(new Column(table, "col2", DataType.Int));
table.PartitionScheme = "MyTablePartitionScheme";
table.PartitionSchemeParameters.Add(new PartitionSchemeParameter(table, "col1"));
//Add PhysicalPartition objects for the partitions on which you like to apply compression.
//Make Partition 5 as Page compressed
table.PhysicalPartitions.Add(new PhysicalPartition(table, 5, DataCompressionType.Page));
//Make Partition 6 and 7 Row compressed
table.PhysicalPartitions.Add(new PhysicalPartition(table, 6, DataCompressionType.Row));
table.PhysicalPartitions.Add(new PhysicalPartition(table, 7, DataCompressionType.Row));
table.Create();
}
// Based on Microsoft's example https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-2017
static void Main(string[] args)
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
ServerConnection cnn;
using (var sqlConnection = new SqlConnection(connectionString))
{
cnn = new ServerConnection(sqlConnection);
}
cnn.Connect();
Console.WriteLine("Connected");
//Create the server object
var server = new Server(cnn);
Console.WriteLine("Create the server object");
const string dataBaseName = @"TestPartition";
try
{
//Drop the database if exists
server.Databases[dataBaseName]?.Drop();
//Create TestPartition database
Console.WriteLine($"Creating the database{dataBaseName}");
var db = new Database(server, dataBaseName);
db.Create();
// Adding four file groups and corespodenting files
ConsoleEx.WriteLine("Adding four file groups", ConsoleColor.Cyan);
for (var i = 1; i < 5; i++)
{
db.FileGroups.Add(new FileGroup(db, $"test{i}fg"));
db.FileGroups[i].Files.Add(new DataFile(db.FileGroups[i], $"test{i}datafile",
$"{server.Information.MasterDBPath}\\{dataBaseName}_{i}.mdf"));
}
// Actually added
db.Alter();
ConsoleEx.WriteLine("Creating the partition function", ConsoleColor.Yellow);
//Create the partition function
var partitionFunction = new PartitionFunction(db, "myRangePF1");
partitionFunction.PartitionFunctionParameters.Add(
new PartitionFunctionParameter(partitionFunction, DataType.Int));
partitionFunction.RangeType = RangeType.Left;
partitionFunction.RangeValues = new object[] { 1, 100, 1000 };
partitionFunction.Create();
// Filegroup test1fg | test2fg | test3fg | test4fg
// Partition 1 | 2 | 3 | 4
// Values col1 <= 1| col1 > 1 AND col1 <= 100 | col1 > 100 AND col1 <= 1000| col1 > 1000
ConsoleEx.WriteLine("Creating the partition scheme", ConsoleColor.Red);
var partitionScheme = new PartitionScheme(db, "myRangePS1")
{
PartitionFunction = "myRangePF1"
};
for (var i = 1; i < 5; i++)
{
partitionScheme.FileGroups.Add($"test{i}fg");
}
partitionScheme.Create();
ConsoleEx.WriteLine("Creating the table", ConsoleColor.Cyan);
var table = new Table(db, "TestTable");
table.Columns.Add(new Column(table, "col1", DataType.Int));
table.PartitionScheme = "myRangePS1";
table.PartitionSchemeParameters.Add(new PartitionSchemeParameter(table, "col1"));
table.Create();
//Insert a few records into newly create table
db.ExecuteNonQuery(@"INSERT INTO DBO.TESTTABLE
VALUES (0), (1), (100), (200), (3000);");
// Examine sys.dm_db_partition_stats
var dataset = db.ExecuteWithResults(
@"SELECT partition_number,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('DBO.TESTTABLE');");
foreach (DataRow row in dataset.Tables[0].Rows)
{
ConsoleEx.WriteLine($"Partition {row["partition_number"]} has {row["row_count"]} rows", ConsoleColor.Magenta);
}
db = null;
}
catch (Exception ex)
{
Console.WriteLine(string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException)
.Select(ex1 => ex1.Message)));
}
if (cnn.IsOpen)
{
cnn.Disconnect();
}
cnn = null;
server = null;
Console.WriteLine("Press any key to exit...");
Console.ReadLine();
}
