经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库运维 » MS SQL Server » 查看文章
SQL 创建分区表
来源：cnblogs　　作者：while(true){}　　时间：2018/11/6 10:07:40　　对本文有异议
（以项目中实际使用的GNSS库为例）
背景：数据量巨大，定时创建月表存放数据，月表中数据存放在不同的文件组中来提高查询效率
 
一、创建数据库，添加文件组
除了逻辑文件和物理文件的分离之外，SQL Server使用文件组还有一个优势，那就是分散IO负载，其实现的原理是：
  • 对于单分区表，数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上，那么SQL Server能同时从不同的物理硬盘上读写数据，把IO负载分散到不同的硬盘上。
  • 对于多分区表，每个分区使用一个文件组，把不同的数据子集存储在不同的磁盘上，SQL Server在读写某一个分组的数据时，能够调用不同的硬盘IO。
这两种方式，其本质上，都是使每个硬盘均摊系统负载，提高IO性能。
  1. CREATE DATABASE [GNSS]
  2.  CONTAINMENT = NONE
  3.  ON  PRIMARY 
  4. ( NAME = N'GNSS', FILENAME = N'D:\Databases\GNSS\GNSS.mdf' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
  5.  FILEGROUP [GNSSFG0]  DEFAULT
  6. ( NAME = N'GNSSFile0', FILENAME = N'D:\Databases\GNSS\GNSSFile0.ndf' , SIZE = 287744KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
  7.  FILEGROUP [GNSSFG1] 
  8. ( NAME = N'GNSSFile1', FILENAME = N'D:\Databases\GNSS\GNSSFile1.ndf' , SIZE = 778240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  9.  FILEGROUP [GNSSFG10] 
  10. ( NAME = N'GNSSFile10', FILENAME = N'D:\Databases\GNSS\GNSSFile10.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  11.  FILEGROUP [GNSSFG11] 
  12. ( NAME = N'GNSSFile11', FILENAME = N'D:\Databases\GNSS\GNSSFile11.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  13.  FILEGROUP [GNSSFG12] 
  14. ( NAME = N'GNSSFile12', FILENAME = N'D:\Databases\GNSS\GNSSFile12.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  15.  FILEGROUP [GNSSFG13] 
  16. ( NAME = N'GNSSFile13', FILENAME = N'D:\Databases\GNSS\GNSSFile13.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  17.  FILEGROUP [GNSSFG14] 
  18. ( NAME = N'GNSSFile14', FILENAME = N'D:\Databases\GNSS\GNSSFile14.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  19.  FILEGROUP [GNSSFG15] 
  20. ( NAME = N'GNSSFile15', FILENAME = N'D:\Databases\GNSS\GNSSFile15.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  21.  FILEGROUP [GNSSFG16] 
  22. ( NAME = N'GNSSFile16', FILENAME = N'D:\Databases\GNSS\GNSSFile16.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  23.  FILEGROUP [GNSSFG17] 
  24. ( NAME = N'GNSSFile17', FILENAME = N'D:\Databases\GNSS\GNSSFile17.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  25.  FILEGROUP [GNSSFG18] 
  26. ( NAME = N'GNSSFile18', FILENAME = N'D:\Databases\GNSS\GNSSFile18.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  27.  FILEGROUP [GNSSFG19] 
  28. ( NAME = N'GNSSFile19', FILENAME = N'D:\Databases\GNSS\GNSSFile19.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  29.  FILEGROUP [GNSSFG2] 
  30. ( NAME = N'GNSSFile2', FILENAME = N'D:\Databases\GNSS\GNSSFile2.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  31.  FILEGROUP [GNSSFG20] 
  32. ( NAME = N'GNSSFile20', FILENAME = N'D:\Databases\GNSS\GNSSFile20.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  33.  FILEGROUP [GNSSFG21] 
  34. ( NAME = N'GNSSFile21', FILENAME = N'D:\Databases\GNSS\GNSSFile21.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  35.  FILEGROUP [GNSSFG22] 
  36. ( NAME = N'GNSSFile22', FILENAME = N'D:\Databases\GNSS\GNSSFile22.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  37.  FILEGROUP [GNSSFG23] 
  38. ( NAME = N'GNSSFile23', FILENAME = N'D:\Databases\GNSS\GNSSFile23.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  39.  FILEGROUP [GNSSFG24] 
  40. ( NAME = N'GNSSFile24', FILENAME = N'D:\Databases\GNSS\GNSSFile24.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  41.  FILEGROUP [GNSSFG25] 
  42. ( NAME = N'GNSSFile25', FILENAME = N'D:\Databases\GNSS\GNSSFile25.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  43.  FILEGROUP [GNSSFG26] 
  44. ( NAME = N'GNSSFile26', FILENAME = N'D:\Databases\GNSS\GNSSFile26.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  45.  FILEGROUP [GNSSFG27] 
  46. ( NAME = N'GNSSFile27', FILENAME = N'D:\Databases\GNSS\GNSSFile27.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  47.  FILEGROUP [GNSSFG28] 
  48. ( NAME = N'GNSSFile28', FILENAME = N'D:\Databases\GNSS\GNSSFile28.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  49.  FILEGROUP [GNSSFG29] 
  50. ( NAME = N'GNSSFile29', FILENAME = N'D:\Databases\GNSS\GNSSFile29.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  51.  FILEGROUP [GNSSFG3] 
  52. ( NAME = N'GNSSFile3', FILENAME = N'D:\Databases\GNSS\GNSSFile3.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  53.  FILEGROUP [GNSSFG30] 
  54. ( NAME = N'GNSSFile30', FILENAME = N'D:\Databases\GNSS\GNSSFile30.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  55.  FILEGROUP [GNSSFG31] 
  56. ( NAME = N'GNSSFile31', FILENAME = N'D:\Databases\GNSS\GNSSFile31.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  57.  FILEGROUP [GNSSFG4] 
  58. ( NAME = N'GNSSFile4', FILENAME = N'D:\Databases\GNSS\GNSSFile4.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  59.  FILEGROUP [GNSSFG5] 
  60. ( NAME = N'GNSSFile5', FILENAME = N'D:\Databases\GNSS\GNSSFile5.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  61.  FILEGROUP [GNSSFG6] 
  62. ( NAME = N'GNSSFile6', FILENAME = N'D:\Databases\GNSS\GNSSFile6.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  63.  FILEGROUP [GNSSFG7] 
  64. ( NAME = N'GNSSFile7', FILENAME = N'D:\Databases\GNSS\GNSSFile7.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  65.  FILEGROUP [GNSSFG8] 
  66. ( NAME = N'GNSSFile8', FILENAME = N'D:\Databases\GNSS\GNSSFile8.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 
  67.  FILEGROUP [GNSSFG9] 
  68. ( NAME = N'GNSSFile9', FILENAME = N'D:\Databases\GNSS\GNSSFile9.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
  69.  LOG ON 
  70. ( NAME = N'GNSS_log', FILENAME = N'D:\Databases\GNSS\GNSS_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
View Code
 

二、创建分区函数和分区方案

分区函数定义了用于分区的数据边界，而分区方案指定了符合分区边界的数据存放在哪个文件组。因此，分区方案中指定的文件组个数应该是比分区函数中指定的边界数大1的。

  1. USE [GNSS]
  2. GO
  4. /****** Object:  PartitionFunction [PF201808]     ******/
  5. CREATE PARTITION FUNCTION [PF201808](datetime) AS RANGE RIGHT FOR VALUES (N'2018-08-02T00:00:00.000', N'2018-08-03T00:00:00.000', N'2018-08-04T00:00:00.000', N'2018-08-05T00:00:00.000', N'2018-08-06T00:00:00.000', N'2018-08-07T00:00:00.000', N'2018-08-08T00:00:00.000', N'2018-08-09T00:00:00.000', N'2018-08-10T00:00:00.000', N'2018-08-11T00:00:00.000', N'2018-08-12T00:00:00.000', N'2018-08-13T00:00:00.000', N'2018-08-14T00:00:00.000', N'2018-08-15T00:00:00.000', N'2018-08-16T00:00:00.000', N'2018-08-17T00:00:00.000', N'2018-08-18T00:00:00.000', N'2018-08-19T00:00:00.000', N'2018-08-20T00:00:00.000', N'2018-08-21T00:00:00.000', N'2018-08-22T00:00:00.000', N'2018-08-23T00:00:00.000', N'2018-08-24T00:00:00.000', N'2018-08-25T00:00:00.000', N'2018-08-26T00:00:00.000', N'2018-08-27T00:00:00.000', N'2018-08-28T00:00:00.000', N'2018-08-29T00:00:00.000', N'2018-08-30T00:00:00.000', N'2018-08-31T00:00:00.000')
  6. GO
  9. USE [GNSS]
  10. GO
  12. /****** Object:  PartitionScheme [PS201808]    ******/
  13. CREATE PARTITION SCHEME [PS201808] AS PARTITION [PF201808] TO ([GNSSFG1], [GNSSFG2], [GNSSFG3], [GNSSFG4], [GNSSFG5], [GNSSFG6], [GNSSFG7], [GNSSFG8], [GNSSFG9], [GNSSFG10], [GNSSFG11], [GNSSFG12], [GNSSFG13], [GNSSFG14], [GNSSFG15], [GNSSFG16], [GNSSFG17], [GNSSFG18], [GNSSFG19], [GNSSFG20], [GNSSFG21], [GNSSFG22], [GNSSFG23], [GNSSFG24], [GNSSFG25], [GNSSFG26], [GNSSFG27], [GNSSFG28], [GNSSFG29], [GNSSFG30], [GNSSFG31])
  14. GO
View Code

 

三、创建分区表

分区表跟普通表创建有点不一样，分区表的创建还需要指定这个分区需要使用哪个分区方案下的分区字段，那么这里就是[PS201808]中的[SignalDateTime]字段。

  1. CREATE TABLE [dbo].[201808](
  2.     [VIN] [char](17) NOT NULL,
  3.     [TerminalCode] [varchar](20) NOT NULL,
  4.     [Latitude] [float] NOT NULL,
  5.     [Longitude] [float] NOT NULL,
  6.     [Direction] [smallint] NOT NULL,
  7.     [Speed] [float] NOT NULL,
  8.     [Elevation] [int] NOT NULL,
  9.     [StateFlag] [bigint] NOT NULL,
  10.     [AlarmFlag] [bigint] NOT NULL,
  11.     [ServerDateTime] [datetime] NOT NULL,
  12.     [SignalDateTime] [datetime] NOT NULL,
  13.     [IsBlind] [bit] NOT NULL,
  14.     [ACCState] [bit] NOT NULL,
  15.     [PositioningState] [bit] NOT NULL,
  16.     [ServerCode] [varchar](20) NOT NULL,
  17.     [TerminalSIM] [varchar](13) NULL,
  18.     [PlateNumber] [nvarchar](8) NULL,
  19.     [ExtraStateFlag] [bigint] NULL,
  20.     [ExtraStateFlag2] [bigint] NULL,
  21.     [ExtraAlarmFlag] [bigint] NULL,
  22.     [ExtraAlarmFlag2] [bigint] NULL,
  23.     [RollerState] [tinyint] NULL,
  24.     [Electricity] [float] NULL,
  25.     [Temperature] [varchar](20) NULL,
  26.     [OilHeight] [float] NULL,
  27.     [Mileage] [float] NULL,
  28.     [OilVolume] [float] NULL,
  29.     [DRSpeed] [float] NULL,
  30.     [SignalStrength] [tinyint] NULL,
  31.     [SatelliteCount] [tinyint] NULL,
  32.     [ExtendedState] [bigint] NULL,
  33.     [IOState] [int] NULL,
  34.     [OverspeedLocationType] [tinyint] NULL,
  35.     [OverspeedAreaOrLineID] [bigint] NULL,
  36.     [InAndOutAreaOrLineLocationType] [tinyint] NULL,
  37.     [InAndOutAreaOrLineID] [bigint] NULL,
  38.     [InAndOutAreaOrLineDirection] [bit] NULL,
  39.     [LineDrivingTimeTooLongOrNotEnoughID] [bigint] NULL,
  40.     [LineDrivingTime] [int] NULL,
  41.     [LineDrivingResult] [bit] NULL
  42. ) ON [PS201808]([SignalDateTime])
View Code

 

 

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MS SQL Server热门文章

  • [sql Server]除非另外还指定了TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效
  • SQL 拼接字符串 使用IN查询方法
  • Microsoft SQL Server 双机热备份，实时同步
  • SQL Server 2016 + AlwaysOn 无域集群
  • Microsoft SQL Server 2012安装说明
  • mssql sqlserver 不固定行转列数据(动态列)
  • 解釋 SQL Server 的狀態值 Runnable、Running、Suspended、Sleeping
  • sql server中format函数的yyyyMMddHHmmssffff时间格式兼容旧版sql写法
  • The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
  • SQL去除数据库表中tab、空格、回车符等特殊字符的解决方法

    • MS SQL Server推荐文章

  • 拯救SQL Server数据库事务日志文件损坏的终极大招
  • SQL Server实战七：自定义数据类型、标量值、内嵌表值、多语句表值函数的操作
  • SQL Server实战六：T-SQL、游标、存储过程的操作
  • SQL Server实战四：查询数据库的数据
  • 模拟SQLserver死锁现象
  • SqlServer的执行计划如何分析？
  • SQL Server实例间同步登录用户
  • SQL Server 2022新功能概览
  • SQL Server 2022 AlwaysOn新特性之包含可用性组介绍
  • 当 SQL Server（mssql-jdbc） 遇上 BigDecimal → 精度丢失，真坑！

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号