Raja_mssqlDBA's questions

我正在尝试捕获现有的 CDC 值并禁用分区截断并再次启用。我无法弄清楚如何在动态 SQL 中处理空值。

如果我直接传递@role_name =NULL，&@filegroup_name = NULL。它工作正常。

-- Enable cdc for that table
    declare @cmd_cdc_enable nvarchar(max)
    SET @cmd_cdc_enable =N'
    exec sys.sp_cdc_enable_table 
    @source_schema = '''+@v_source_schema+''', 
    @source_name = '''+@v_source_table+''',
    @capture_instance = '''+@v_capture_instance +''',
    @supports_net_changes = 1,
    @role_name     =NULL,
    @filegroup_name = NULL,
    @allow_partition_switch = 1

示例代码：

GO
EXEC sys.sp_cdc_enable_db
GO


create schema [C1810429]
go


create table [C1810429].[STYTOTAL_RAW_NEW] (ID int identity primary key, name varchar(200))

-- Enable First time
go
EXEC sys.sp_cdc_enable_table  
    @source_schema = N'C1810429',  
    @source_name   = N'STYTOTAL_RAW_NEW', 
    @capture_instance = 'C1810429_STYTOTAL_RAW_NEW',
    @supports_net_changes = 1 ,
    @role_name     = NULL,  
    @filegroup_name = NULL, 
    @allow_partition_switch = 1



drop table if exists tbl_sp_cdc_help_change_data_capture

create table tbl_sp_cdc_help_change_data_capture (
source_schema sysname null, source_table sysname null,  capture_instance sysname null,  object_id int null, source_object_id int null,  
start_lsn binary(10) null,  end_lsn binary(10) null, supports_net_changes bit null,has_drop_pending bit null,   role_name sysname null, 
index_name sysname null,    filegroup_name sysname null,    create_date datetime null,  index_column_list nvarchar(max) null,   captured_column_list nvarchar(max) null
)

insert into tbl_sp_cdc_help_change_data_capture
Exec sys.sp_cdc_help_change_data_capture @source_schema='C1810429',@source_name='STYTOTAL_RAW_NEW'

-- select * from tbl_sp_cdc_help_change_data_capture



    declare @v_source_schema sysname
    declare @v_source_table sysname 
    declare @v_capture_instance sysname
    declare @v_supports_net_changes int
    declare @v_role_name sysname
    declare @v_filegroup_name sysname

    select @v_source_schema=source_schema,@v_source_table=source_table,@v_capture_instance=capture_instance,@v_supports_net_changes=supports_net_changes,
    @v_role_name=role_name,@v_filegroup_name=[filegroup_name]
    from tbl_sp_cdc_help_change_data_capture

    --select @v_source_schema,@v_source_table,@v_capture_instance,@v_supports_net_changes,@v_role_name,@v_filegroup_name

    /*
    -- disable cdc for that table
    declare @cmd_CDC_disable nvarchar(max)
    SET @cmd_CDC_disable =N'
    exec sys.sp_cdc_disable_table 
    @source_schema = '''+@v_source_schema+''', 
    @source_name = '''+@v_source_table+''',
    @capture_instance = '''+@v_capture_instance+'''

    '
    --print @cmd_CDC_disable
    EXEC sys.sp_executesql @cmd_CDC_disable

    select 'Disabled'

    */
    

    --select @v_source_schema,@v_source_table,@v_capture_instance,@v_supports_net_changes,@v_role_name,@v_filegroup_name

    -- Enable cdc for that table
    declare @cmd_cdc_enable nvarchar(max)
    SET @cmd_cdc_enable =N'
    exec sys.sp_cdc_enable_table 
    @source_schema = '''+@v_source_schema+''', 
    @source_name = '''+@v_source_table+''',
    @capture_instance = '''+@v_capture_instance +''',
    @supports_net_changes = 1,
    @role_name     ='''+@v_role_name  +''',
    @filegroup_name = '''+@v_filegroup_name  +''',
    @allow_partition_switch = 1

    '
    print @cmd_cdc_enable

    EXEC sys.sp_executesql @cmd_cdc_enable

    --select 'Enabled'

我有一个要求，其中涉及两个主要表，一个是暂存表 - 来自其他源 RDBMS 的 DLSTYTOTAL 数据，另一个表是分区表 -STYTOTAL_RAW，我们正在其中加载传入数据。

1. 从源分区表自动生成下一个标识值

为了进行分区，我创建了另一个暂存表 - STYTOTAL_RAW_Intermediate_Staging。因为 DLSTYTOTAL 表不包含所有列，特别是标识列和分区键列。

主暂存表有 1000 多万条数据。我想过每次删除时使用 select * into 并将其创建为分区暂存表，然后切换到分区表。问题是我无法获得正确的 IDENTITY 值，因为它每次都会重置。我想过从分区表 STYTOTAL_RAW 中取最大值加 1。但是 select IDENTITY(INT,@IDENTITY_no_max ,1) AS [STYTOTALID] 不接受变量

declare @IDENTITY_no_max bigint
select  max([STYTOTALID]) as [Original_max] from STYTOTAL_RAW -- original max value
select @IDENTITY_no_max= max([STYTOTALID]+1) from STYTOTAL_RAW
select @IDENTITY_no_max as [@IDENTITY_no_max]
    
select IDENTITY(INT,1,@IDENTITY_no_max) AS [STYTOTALID],ISNULL([STYLE],0) [STYLE],DATEADD(MICROSECOND, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSDATETIME()) [InsertedDateTime],
CAST(1 AS bit)  as [IsCurrent],[DELDATTIM],[DELFLAG]
into [STYTOTAL_RAW_Intermediate_Staging] from [C1810429].[DLSTYTOTAL]

2. 将日期列 [InsertedDateTime] 从 select * 更改为其自身，使其不为空。

   选择 DATEADD(MICROSECOND, ROW_NUMBER() OVER (ORDER BY ( SELECT NULL)), isnull(SYSDATETIME(),'1900-01-01 00:00:00.000') ) [InsertedDateTime] 进入 tbl_date_not_null sp_help'tbl_date_not_null'

有没有办法始终从源表中获取标识值并填充到暂存中。此外，是否可以在 select * 中将日期列更改为 not null，而不是 alter table with not null。

感谢您的帮助。

在此处添加脚本：https://dbfiddle.uk/uaMhsB4k

这篇文章延续了表分区设计的建议。

我尝试过截断旧分区并合并它。但我收到错误“为表‘dbo.STYTOTAL_RAW’指定的分区号 8 无效，分区号范围为 1 到 6。”看起来一旦合并，while 循环对于最大分区号就变得无效。

为了测试我使用了 3 天，但最初的要求是删除超过 90 天的分区。

按照@DanGuzman 的说法，也添加了 PF。完整脚本可用。在我的第一个帖子中。我也已将其链接到此帖子的顶部。

--Create function, copy from your script
CREATE PARTITION FUNCTION PF_myDateRange ( [datetime2](7))
AS RANGE RIGHT FOR VALUES 
(
'2024-06-01 23:59:59.9999999',
'2024-07-01 23:59:59.9999999',
'2024-08-01 23:59:59.9999999',
'2024-09-01 23:59:59.9999999',
'2024-10-01 23:59:59.9999999',
'2024-10-21 23:59:59.9999999' -- take the max day and round with 12 AM ex: 2024-10-21 00:00:00.0000000
)
GO
use DB_Partition

--Invalid partition number 8 specified for table 'dbo.STYTOTAL_RAW', partition number can range from 1 to 6.

-- We can loop through the partition number directly from the table
--TRUNCATE TABLE [STYTOTAL_RAW] WITH (PARTITIONS (4));

declare @cmd_1 nvarchar(max)
declare @cmd_2 nvarchar(max)
DECLARE @partition_no bigint
DECLARE @PartitionFunction_name nvarchar(128)
DECLARE @PartitionFunction_Upper_value datetime2(7)
DECLARE @minrow int
DECLARE @maxrow int


select  @minrow = MIN(p.partition_number), @maxrow  = MAX(p.partition_number)
    from sys.indexes i  
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
    join sys.partition_functions pf on pf.function_id = ps.function_id  
    left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id   
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id  
    where i.object_id = object_id('STYTOTAL_RAW') 
    and rv.value < DATEADD(DAY, -3, SYSDATETIME()) 
    

select @minrow,@maxrow


while (@minrow <=@maxrow)
 begin

select  @partition_no=partition_number,@PartitionFunction_name=pf.name,@PartitionFunction_Upper_value=cast(rv.value as datetime2(7))
    from sys.indexes i  
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
    join sys.partition_functions pf on pf.function_id = ps.function_id  
    left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id   
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id  
    where i.object_id = object_id('STYTOTAL_RAW') 
    and rv.value < DATEADD(DAY, -3, SYSDATETIME()) 
    and p.partition_number = @minrow



SET @cmd_1 = N'TRUNCATE TABLE dbo.STYTOTAL_RAW WITH (PARTITIONS (' + convert(NVARCHAR(128),@partition_no) + N'));'
print @cmd_1
--EXEC sys.sp_executesql @cmd_1
SET @cmd_2 = N'ALTER PARTITION FUNCTION ['+ @PartitionFunction_name+ '] () merge range ('''+convert (NVARCHAR(128), @PartitionFunction_Upper_value) +''');'


print @cmd_2
--EXEC sys.sp_executesql @cmd_2

set @minrow =@minrow +1 
end

请提出任何建议。感谢您的帮助。

我正在寻找表分区设计帮助，以便在 UAT 和 PROD 中实现。

当前流程是从 SP 将数据从暂存表插入到主表，并使用业务逻辑更新表并删除超过 90 天的数据。主表是 120 GB 表，包含 1 亿数据，插入、更新和删除需要更多时间，大约 1 小时才能完成该过程。我在这里发布了 --> ETL 数据加载 DML SP 缓慢建议

此过程每天运行 4 次。我正在尝试使用表分区测试并自动化此过程。问题是我每天加载数据 4 次，有时我们还会根据要求运行额外的批处理。我无法使用每日或每月分区，因为我无法在数据存在的情况下切换。我相信我需要使用基于最大当前日期的每次加载分区。有人可以帮忙吗？

除了主键作为聚集索引之外，我们没有其他索引。这是我测试过的测试和示例。

表创建：

--select top 10 * from [C1810429].[STYTOTAL_RAW]

use master
go
alter database DB_Partition set single_user with rollback immediate
drop database DB_Partition
create database DB_Partition
go
use master
go

go
use DB_Partition
go
create schema [C1810429]

go
--*/
use DB_Partition
go

---1
--drop table [C1810429].[STYTOTAL_RAW]
CREATE TABLE [C1810429].[STYTOTAL_RAW](
    [STYTOTALID] [int] IDENTITY(1,1) NOT NULL,
    [STYLE] [decimal](5, 0) NOT NULL,
    [InsertedDateTime] [datetime2](7) NOT NULL,
    [UpdatedDateTime] [datetime2](7) NULL,
    [IsCurrent] [bit] NULL,
    [DELDATTIM] [datetime2](7) NULL,
    [DELFLAG] [char](1) NULL,
    --/*
 CONSTRAINT [PK_C1810429_STYTOTAL_RAW] PRIMARY KEY CLUSTERED 
(
    [STYTOTALID] ASC,
    [STYLE] ASC,
    [InsertedDateTime] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)  
--*/
)

--*/


--drop table [C1810429].[DLSTYTOTAL]
CREATE TABLE [C1810429].[DLSTYTOTAL](
    [STYLE] [decimal](5, 0) NULL,   
    [DELDATTIM] [datetime2](7) NULL,
    [DELFLAG] [char](1) NULL
)

插入样本：

use DB_Partition

SET NOCOUNT ON
DECLARE @DateTime DATETIME2(7) = '2024-05-01 00:00:00.000'

WHILE @DateTime <= '2024-10-18 23:59:59.999'
BEGIN

 INSERT INTO [C1810429].[STYTOTAL_RAW] ([STYLE],[InsertedDateTime])

 SELECT  ABS(CHECKSUM(NEWID())%8)+1,@DateTime

 SET @DateTime = DATEADD(Minute, 1, @DateTime)
 

END
GO

下一插入/批次示例：

/*
use DB_Partition
-- populate further test data
SET NOCOUNT ON
DECLARE @DateTime DATETIME2(7) = '20240701'
    
WHILE @DateTime <= '20241016'
BEGIN
    
     insert into [C1810429].[DLSTYTOTAL] ([STYLE])
    
     SELECT  ABS(CHECKSUM(NEWID())%8)+1
    
     SET @DateTime = DATEADD(Minute, 1, @DateTime)
     
END
GO
*/
INSERT INTO  [C1810429].[STYTOTAL_RAW] ([STYLE],[DELDATTIM],[DELFLAG],[InsertedDateTime],[IsCurrent])     

SELECT STG.[STYLE],STG.[DELDATTIM],STG.[DELFLAG],DATEADD(MICROSECOND, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSDATETIME()) as [InsertedDateTime],1   as [IsCurrent]  
FROM [C1810429].[DLSTYTOTAL] STG Left Join [C1810429].[STYTOTAL_RAW] TGT  
on STG.STYLE = TGT.STYLE AND STG.DELDATTIM = TGT.DELDATTIM
Where TGT.DELDATTIM is null

更新和删除：

--update
UPDATE [C1810429].[STYTOTAL_RAW]
SET ISCURRENT = 0,UpdatedDateTime = SYSDATETIME()                                      
WHERE IsCurrent = 1 

--delete
delete FROM [C1810429].[STYTOTAL_RAW] 
WHERE InsertedDateTime < DATEADD(DAY, -90, SYSDATETIME()) and IsCurrent = 0 

感谢您的帮助。

我已经测试了这个月度分区：

use DB_Partition
go
--drop PARTITION FUNCTION PF_myDateRange

CREATE PARTITION FUNCTION PF_myDateRange ( [datetime2](7))
AS RANGE RIGHT FOR VALUES 
(
'20240601',
'20240701',
'20240801',
'20240901',
'20241001',
'20241101'
)
GO
-- not sure I need to time as well in the function
CREATE PARTITION FUNCTION PF_myDateRange ( [datetime2](7))
AS RANGE RIGHT FOR VALUES 
(
'2024-06-01 23:59:59.997',
'2024-07-01 23:59:59.997',
'2024-08-01 23:59:59.997',
'2024-09-01 23:59:59.997',
'2024-10-01 23:59:59.997',
'2024-11-01 23:59:59.997'
)
GO
CREATE PARTITION SCHEME PS_myPartitionScheme AS PARTITION PF_myDateRange ALL TO ([PRIMARY]);

-- drop and create index to make existing table into partition data alignment

CREATE UNIQUE CLUSTERED INDEX [PK_C1810429_STYTOTAL_RAW] ON [C1810429].[STYTOTAL_RAW]
(
    [STYTOTALID] ASC,
    [STYLE] ASC,
    [InsertedDateTime] ASC
) 
WITH (DROP_EXISTING=ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON PS_myPartitionScheme([InsertedDateTime]);

已编辑：我尝试使用最大时间和+1 秒等进行自动化，但是当我加载 2 和 3 等时，数据在切换时会转到不同的分区边界。

---1
--drop table [C1810429].[STYTOTAL_RAW_Intermediate_Staging]
CREATE TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging(
    [STYTOTALID] [int] IDENTITY(1,1) NOT NULL,
    [STYLE] [decimal](5, 0) NOT NULL,
    [InsertedDateTime] [datetime2](7) NOT NULL,
    [UpdatedDateTime] [datetime2](7) NULL,
    [IsCurrent] [bit] NULL,
    [DELDATTIM] [datetime2](7) NULL,
    [DELFLAG] [char](1) NULL,
    --/*
 CONSTRAINT [PK_C1810429_STYTOTAL_RAW_Intermediate_Staging] PRIMARY KEY CLUSTERED 
(
    [STYTOTALID] ASC,
    [STYLE] ASC,
    [InsertedDateTime] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)  

) on [Primary]--on PS_myPartitionScheme([InsertedDateTime]);

----

use DB_Partition
--truncate table C1810429.STYTOTAL_RAW_Intermediate_Staging
select * from C1810429.STYTOTAL_RAW_Intermediate_Staging
--00,06,12,18,23:59.59

-- load 1
select max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW]
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 00:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 01:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 11:59:59.999')

-- load 2
select max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW]
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 12:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 13:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 17:59:59.999')

-- load 3
select max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW]
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 18:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 19:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 23:59:59.999')

--create new partitions for new data load use the primary FG
--get the staging table max date and add one minute and create new boundary
select max([InsertedDateTime]) from [C1810429].STYTOTAL_RAW_Intermediate_Staging

Declare @New_PartitionBoundaryDate_Plus datetime2 (7)
select @New_PartitionBoundaryDate_Plus = max (DATEADD(minute,+1,[InsertedDateTime])) from [C1810429].STYTOTAL_RAW_Intermediate_Staging
select @New_PartitionBoundaryDate_Plus as [Plus]

--load 1  max([InsertedDateTime])
7   25920   2024-10-19 00:00:00.000
8   0   NULL

--load 2  max([InsertedDateTime])+ 1 minute

ALTER PARTITION SCHEME PS_myPartitionScheme     NEXT USED [Primary];
ALTER PARTITION FUNCTION PF_myDateRange ()  SPLIT RANGE('2024-10-19 11:59:59.9970000');
---------
--select *  from [C1810429].[STYTOTAL_RAW_Intermediate_Staging]
select min([InsertedDateTime]) ,max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW_Intermediate_Staging]
 
-- pass the min and max value of staging table
--load 1 >=  min([InsertedDateTime]) <=max([InsertedDateTime])
--load 2 >=  min([InsertedDateTime]) <=max([InsertedDateTime])

8   3   2024-10-19 12:00:59.997 -- Here the data is failling under
9   0   2024-10-19 18:00:59.997
10  0   NULL

ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
WITH CHECK ADD CONSTRAINT ck_Min_InsertedDateTime
CHECK ([InsertedDateTime] IS NOT NULL AND [InsertedDateTime] >= '2024-10-19 00:00:00.0000000' )

ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
WITH CHECK ADD CONSTRAINT ck_Max_InsertedDateTime
CHECK ([InsertedDateTime] IS NOT NULL AND [InsertedDateTime] <= '2024-10-19 11:59:59.9970000' )

-- get the data for bounday and enter the partition no or date

-- Create partition for each load by getting max datetime of [InsertedDateTime] in table  [C1810429].[STYTOTAL_RAW]
--2024-10-19 12:00:00.001
Declare @New_PartitionBoundaryDate_SWITCH datetime2 (7)
select @New_PartitionBoundaryDate_SWITCH = max (DATEADD(minute,0,[InsertedDateTime]))  from [C1810429].STYTOTAL_RAW_Intermediate_Staging
select @New_PartitionBoundaryDate_SWITCH as [SWITCH] --Get the swith parttion

--load 1 is not working since the last data is in that boundary value
ALTER TABLE   [C1810429].STYTOTAL_RAW_Intermediate_Staging SWITCH TO [C1810429].[STYTOTAL_RAW] PARTITION $PARTITION.PF_myDateRange('2024-10-19 11:59:59.997');

--load 1 taken last not null boundary partition no
--load 1 taken last not null boundary partition no
ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging SWITCH TO [C1810429].[STYTOTAL_RAW] PARTITION 8 -- 2024-10-19 12:00:00.001

declare @ck_Min_InsertedDateTime_drop varchar (max)
set @ck_Min_InsertedDateTime_drop = 'ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
drop CONSTRAINT ck_Min_InsertedDateTime;'
--select @ck_Min_InsertedDateTime_drop
Exec (@ck_Min_InsertedDateTime_drop)

declare @ck_Max_InsertedDateTime_drop varchar (max)
set @ck_Max_InsertedDateTime_drop = 'ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
drop CONSTRAINT ck_Max_InsertedDateTime;'
--select @@ck_Max_InsertedDateTime_drop
Exec (@ck_Max_InsertedDateTime_drop)

作为从发布者向订阅者获取新数据的要求。我使用生成新快照并运行日志读取器并运行分发代理。

想知道我们使用哪个选项来初始化所有文章的订阅者。我可以看到以下 4 个选项。

复制工作良好，但我从应用程序团队那里听说他们面临一些比较数据和加载 ETL 作业的问题。

我使用了删除并重新创建，它说主键和外键关系错误。然后我使用截断所有表并加载。

这是正确的方法吗。