SQL Server表分区

最新推荐文章于 2024-02-28 03:43:01 发布

原创最新推荐文章于 2024-02-28 03:43:01 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #SQL SERVER #SQL Server #SQL server #优化 #分区函数 #架构 #表分区

SQL Server 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了一种针对大量数据的SQL Server数据库分区方案。通过创建数据库、文件组及分区函数等步骤，实现了数据的有效管理和查询优化。此外，还提供了具体的T-SQL脚本实例，演示如何创建分区表及填充数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近工作中由于某些表数据量过大，对表进行分区从而优化了查询效率，下面贴出来与大家分享一下：

--1 、创建数据库 

CREATE DATABASE BigDataTestDB 

--2 、创建文件组 

ALTER DATABASE BigDataTestDB ADD FILEGROUP YEARFG1 

ALTER DATABASE BigDataTestDB ADD FILEGROUP YEARFG2 

ALTER DATABASE BigDataTestDB ADD FILEGROUP YEARFG3 

ALTER DATABASE BigDataTestDB ADD FILEGROUP YEARFG4 

  

--3 、为文件组添加文件来进行物理数据存储 

ALTER DATABASE BigDataTestDB ADD FILE ( NAME = 'YEARF1' , FILENAME = 'C:\ADVWORKSF1.NDF' ) TO FILEGROUP YEARFG1 ; 

ALTER DATABASE BigDataTestDB ADD FILE ( NAME = 'YEARF2' , FILENAME = 'C:\ADVWORKSF2.NDF' ) TO FILEGROUP YEARFG2 ; 

ALTER DATABASE BigDataTestDB ADD FILE ( NAME = 'YEARF3' , FILENAME = 'C:\ADVWORKSF3.NDF' ) TO FILEGROUP YEARFG3 ; 

ALTER DATABASE BigDataTestDB ADD FILE ( NAME = 'YEARF4' , FILENAME = 'C:\ADVWORKSF4.NDF' ) TO FILEGROUP YEARFG4 ; 

  

--4 、创建分区函数 

USE BigDataTestDB ; 

GO

  

CREATE PARTITION FUNCTION YEARPF ( datetime ) 

AS

RANGE LEFT FOR VALUES ( '01/01/2010' , '01/01/2011' , '01/01/2012' ) 

  

--5 、创建分区架构 

CREATE PARTITION SCHEME YEARPS 

AS PARTITION YEARPF TO ( YEARFG1 , YEARFG2 , YEARFG3 , YEARFG4 ) 

  

--6 、创建使用此 SCHEME 的表 

CREATE TABLE PARTITIONTEST 

(

ID INT NOT NULL IDENTITY (1,1),

CONTITLE VARCHAR (500)NULL,

USERNAME VARCHAR (100)NULL,

CONTEXT TEXT NULL, 

HITCOUNT INT NULL, 

CREATETIME DATETIME not NULL 

) ON   YEARPS ( CREATETIME ) 

  

--7 、填充数据 

DECLARE @DT datetime , @NUM INT , @N INT 

SET @DT = CONVERT ( varchar (12),'2009-01-01',23)

SET @N =1

WHILE @N >=0

BEGIN

  SET @NUM =1

  WHILE @NUM <=10000

    BEGIN 

       INSERT INTO dbo . PARTITIONTEST ( CONTITLE , USERNAME , CONTEXT , HITCOUNT , CREATETIME ) 

       VALUES ( CONVERT ( varchar (12),@DT,23)+' 文章，编号： ' + CAST ( @NUM AS VARCHAR (5)),' 用户 ' + CAST ( @NUM AS VARCHAR (5)),CONVERT(varchar(12),@DT,23)+' 文章内容： ' + CAST ( @NUM AS VARCHAR (5)),@NUM,@DT)

       SET @NUM +=1

    END 

  SET @DT = DATEADD ( DAY ,1,@DT)

  SET @N = datediff ( day , CONVERT ( varchar (12),@DT,23),'2012-12-09')

END

  

--8 、查看插入数据的分布 

SELECT    *, $PARTITION . YEARPF ( CREATETIME ) FROM   dbo . PARTITIONTEST 

--9 、查看分区的数据量的分布 

SELECT * FROM SYS . partitions WHERE OBJECT_ID = OBJECT_ID ( 'PARTITIONTEST' ) 

  

  

--******** 修改分区 ********

--1 、更改分区架构 , 让下一个分区使用已经存在的分区 YEARFG4 分区 

ALTER PARTITION SCHEME YEARPS NEXT USED YEARFG4 ; 

--2 、更改分区函数 

ALTER PARTITION FUNCTION YEARPF () SPLIT RANGE ( '01/01/2013' ) 

SELECT    *, $PARTITION . YEARPF ( CREATETIME ) FROM   dbo . PARTITIONTEST ; 

SELECT * FROM SYS . partitions WHERE OBJECT_ID = OBJECT_ID ( 'PARTITIONTEST' )