数据库分区(2017-8-7)

来源：互联网发布：hbase删除数据编辑：程序博客网时间：2024/06/13 13:35

--添加文件组ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2017_8]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2017_9]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2017_10]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2017_11]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2017_12]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_1]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_2]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_3]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_4]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_5]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_6]ALTER DATABASE KJ70N ADD FILEGROUP [HistoryPowerData_2018_7]--添加文件和文件组ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2017_8',  FILENAME='E:\data\HistoryPowerData_2017_8.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2017_8ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2017_9',  FILENAME='E:\data\HistoryPowerData_2017_9.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2017_9ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2017_10', FILENAME='E:\data\HistoryPowerData_2017_10.NDF', SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2017_10ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2017_11', FILENAME='E:\data\HistoryPowerData_2017_11.NDF', SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2017_11ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2017_12', FILENAME='E:\data\HistoryPowerData_2017_12.NDF', SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2017_12ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_1',  FILENAME='E:\data\HistoryPowerData_2018_1.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_1ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_2',  FILENAME='E:\data\HistoryPowerData_2018_2.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_2ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_3',  FILENAME='E:\data\HistoryPowerData_2018_3.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_3ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_4',  FILENAME='E:\data\HistoryPowerData_2018_4.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_4ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_5',  FILENAME='E:\data\HistoryPowerData_2018_5.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_5ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_6',  FILENAME='E:\data\HistoryPowerData_2018_6.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_6ALTER DATABASE KJ70N ADD FILE(NAME=N'HistoryPowerData_2018_7',  FILENAME='E:\data\HistoryPowerData_2018_7.NDF',  SIZE=5MB, FILEGROWTH=10MB)TO FILEGROUP HistoryPowerData_2018_7GO--创建分区函数CREATE PARTITION FUNCTION HistoryPowerData_Partition_Fun (datetime)   AS RANGE RIGHT FOR VALUES('2017-9-1 00:00:00','2017-10-1 00:00:00','2017-11-1 00:00:00','2017-12-1 00:00:00','2018-1-1 00:00:00','2018-2-1 00:00:00','2018-3-1 00:00:00','2018-4-1 00:00:00','2018-5-1 00:00:00','2018-6-1 00:00:00','2018-7-1 00:00:00')  GO--创建分区方案CREATE PARTITION SCHEME  HistoryPowerData_Partition   AS PARTITION  HistoryPowerData_Partition_Fun   TO (      HistoryPowerData_2017_8,       HistoryPowerData_2017_9,       HistoryPowerData_2017_10,       HistoryPowerData_2017_11,       HistoryPowerData_2017_12,       HistoryPowerData_2018_1,       HistoryPowerData_2018_2,       HistoryPowerData_2018_3,       HistoryPowerData_2018_4,   HistoryPowerData_2018_5,       HistoryPowerData_2018_6,       HistoryPowerData_2018_7 );GO--创建表CREATE TABLE [dbo].[HistoryPowerData] (    [ID]Nvarchar(20)NOT NULL,[Time]DateTimeNOT NULL,[State]smallintNOT NULL,    [IV1]FLOAT (53)      NOT NULL,    [IA1]FLOAT (53)      NOT NULL,    [IV2]FLOAT (53)      NOT NULL,    [IA2]FLOAT (53)      NOT NULL,    [IV3]FLOAT (53)      NOT NULL,    [IA3]FLOAT (53)      NOT NULL,    [V]FLOAT (53)      NOT NULL,    [Temperature]FLOAT (53)  NOT NULL,    [Quantity]FLOAT (53)      NOT NULL,    [DcorAC]      smallintNOT NULL,)ON  HistoryPowerData_Partition([Time]);GO--创建聚簇索引CREATE CLUSTERED INDEX [HistoryPowerData_CLUSTER_Index]    ON [dbo].[HistoryPowerData]([ID] ASC, [Time] ASC)On HistoryPowerData_Partition([Time]);GO

测试代码:

using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Database_Partition_Test{    class Program    {        public static void Database_Partition_Test(String connStr, DataTable dt, String TableName)        {            using (SqlConnection conn = new SqlConnection(connStr))            {                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connStr, SqlBulkCopyOptions.UseInternalTransaction))                {                    try                    {                        sqlbulkcopy.BulkCopyTimeout = 60;                        sqlbulkcopy.DestinationTableName = TableName;       //目标表名                        for (int i = 0; i < dt.Columns.Count; i++)          //列名添加映射                        {                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);                        }                        sqlbulkcopy.WriteToServer(dt);                    }                    catch (System.Exception ex)                    {                        throw ex;                    }                }            }        }        static void Main(string[] args)        {            string connStr = "server=172.16.19.192;database=KJ70N;uid=sa;pwd=sa";            DataTable dt = new DataTable();            dt.Columns.Add("ID", Type.GetType("System.String"));            dt.Columns.Add("Time", Type.GetType("System.DateTime"));            dt.Columns.Add("State", Type.GetType("System.UInt16"));            dt.Columns.Add("IV1", Type.GetType("System.Single"));            dt.Columns.Add("IA1", Type.GetType("System.Single"));            dt.Columns.Add("IV2", Type.GetType("System.Single"));            dt.Columns.Add("IA2", Type.GetType("System.Single"));            dt.Columns.Add("IV3", Type.GetType("System.Single"));            dt.Columns.Add("IA3", Type.GetType("System.Single"));            dt.Columns.Add("V", Type.GetType("System.Single"));            dt.Columns.Add("Temperature", Type.GetType("System.Single"));            dt.Columns.Add("Quantity", Type.GetType("System.Single"));            dt.Columns.Add("DcorAC", Type.GetType("System.UInt16"));            DateTime now = DateTime.Now;            for (long i = 1; i < 200000; ++i)            {                DataRow dr = dt.NewRow();                dr["State"] = 1;                dr["IV1"] = 1.0;                dr["IA1"] = 1.0;                dr["IV2"] = 1.0;                dr["IA2"] = 1.0;                dr["IV3"] = 1.0;                dr["IA3"] = 1.0;                dr["V"] = 1.0;                dr["Temperature"] = 1.0;                dr["Quantity"] = 1.0;                dr["DcorAC"] = 1;                dr["ID"] = i.ToString();                dr["Time"] = now.AddDays(i);                dt.Rows.Add(dr.ItemArray);            }            DateTime t = DateTime.Now;            Database_Partition_Test(connStr, dt, "HistoryIntelligentPower");            Console.WriteLine(DateTime.Now - t);            Console.ReadKey();        }    }}

阅读全文

1 0