如何向具有主聚簇索引的现有表添加分区

Question

我想将分区添加到具有主聚集索引的现有表中，但我不知道方法：

CREATE TABLE [dbo].[AppUsers]
(
    [Id] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](max) NULL,
    [NormalizedUserName] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [NormalizedEmail] [nvarchar](max) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [Status] [int] NOT NULL,
    [UserType] [int] NOT NULL,
    [CompanyId] [int] NULL,
    [RoleLevelId] [nvarchar](450) NULL,
    [JobCategoryId] [int] NULL,
    [PerfectureId] [int] NULL,
    [CompanySizeId] [int] NULL,
    [DistrictId] [int] NULL,
    [DateCreated] [datetime2](7) NOT NULL,
    [CompanySizeId1] [int] NULL,
    [JobCategoryId1] [int] NULL,
    [PartitionValue] [int] NULL,

    CONSTRAINT [PK_AppUsers] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY]  TEXTIMAGE_ON [PRIMARY] 
GO

这是我的方案：

ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_2', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_2.ndf') TO FILEGROUP bf_2 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_4', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_4.ndf') TO FILEGROUP bf_4 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_6', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_6.ndf') TO FILEGROUP bf_6 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_8', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_8.ndf') TO FILEGROUP bf_8 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_10', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_10.ndf') TO FILEGROUP bf_10 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_12', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_12.ndf') TO FILEGROUP bf_12 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_14', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_14.ndf') TO FILEGROUP bf_14 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_16', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_16.ndf') TO FILEGROUP bf_16 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_18', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_18.ndf') TO FILEGROUP bf_18 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_20', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_20.ndf') TO FILEGROUP bf_20
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_22', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_22.ndf') TO FILEGROUP bf_22 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_24', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_24.ndf') TO FILEGROUP bf_24 
ALTER DATABASE YayoiTest ADD FILE (NAME = N'bf_26', FILENAME = N'C:\Users\Ruby\Desktop\partitest\bf_26.ndf') TO FILEGROUP bf_26
ALTER DATABASE YayoiTest ADD FILE (NAME = N'af_26', FILENAME = N'C:\Users\Ruby\Desktop\partitest\af_26.ndf') TO FILEGROUP af_26

USE YayoiTest
GO
CREATE PARTITION FUNCTION PFunc_NGD(int) AS
RANGE RIGHT FOR VALUES (2,4,6,8,10,12,14,16,18,20,22,24,26)
GO
CREATE PARTITION SCHEME PScheme_NGD AS PARTITION PFunc_NGD
TO (bf_2, bf_4, bf_6, bf_8, bf_10, bf_12, bf_14, bf_16, bf_18, bf_20, 
bf_22, bf_24, bf_26, af_26)

这是我的分区列

[PartitionValue]

有人能帮助我或给我一些建议吗?我刚开始学习分区表

Answer 1

这是我在运行sql server的分区向导工具后得到的脚本。

ALTER TABLE [dbo].[AppUsers] DROP CONSTRAINT [PK_AppUsers] WITH ( ONLINE = OFF )


ALTER TABLE [dbo].[AppUsers] ADD  CONSTRAINT [PK_AppUsers] PRIMARY KEY NONCLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON 
[PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_PScheme_NGD_636849811615387501] ON [dbo]. 
[AppUsers]
(
   [PartitionValue]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PScheme_NGD] 

​