Raja_mssqlDBA's questions

Estou tentando capturar o valor CDC existente e desabilitar e habilitar novamente para truncamento de partição. Não consegui descobrir como lidar com valores nulos em SQL dinâmico.

Se eu passar diretamente @role_name =NULL, & @filegroup_name = NULL. Está funcionando bem.

-- 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

Código de exemplo:

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'

Tenho um requisito em que há duas tabelas principais envolvidas: uma é a tabela de preparação - dados DLSTYTOTAL vindos de outro RDBMS de origem e a outra é a tabela particionada - STYTOTAL_RAW, onde estamos carregando os dados de entrada.

1. Gerar automaticamente o próximo valor de identidade da tabela particionada de origem

Para fazer a partição, criei mais uma tabela de preparação chamada - STYTOTAL_RAW_Intermediate_Staging. Já que a tabela DLSTYTOTAL não tem todas as colunas, particularmente a coluna de identidade e as colunas de chave de partição.

A tabela de preparação principal obtém 10 milhões + dados. Pensei em usar select * into com cada queda de tempo e criá-la na tabela de preparação de partição e então alternar para a tabela de partição. O problema é que não consegui obter o valor IDENTITY correto, pois ele era redefinido a cada vez. Pensei em pegar o valor máximo mais 1 da tabela de partição - STYTOTAL_RAW. Mas select IDENTITY(INT,@IDENTITY_no_max ,1) AS [STYTOTALID] isso não está aceitando variável

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. Alterar a coluna de data [InsertedDateTime] para não nula do select * para si mesma.

   selecione DATEADD(MICROSEGUNDO, NÚMERO_DA_LINHA() SOBRE (ORDEM POR (SELECIONE NULO)), é nulo(SYSDATETIME(),'1900-01-01 00:00:00.000') ) [Data_Hora_Inserida] em tbl_date_not_null sp_help 'tbl_date_not_null'

Existe alguma maneira de obter o valor de identidade o tempo todo da tabela de origem e preencher para staging . Também é possível alterar a coluna de data para not null no select * em si mesmo em vez de alter table com not null.

Obrigado pela ajuda.

Adicionando o script aqui: https://dbfiddle.uk/uaMhsB4k

Esta postagem dá continuidade à sugestão de design de partição de tabela .

Tentei truncar a partição mais antiga e mesclá-la. Mas estou recebendo um erro "Número de partição inválido 8 especificado para a tabela 'dbo.STYTOTAL_RAW', o número da partição pode variar de 1 a 6." Parece que, depois de mesclar, o loop while está ficando inválido para o número máximo de partição.

Para testes, usei 3 dias, mas o requisito original é excluir a partição com mais de 90 dias.

Conforme @DanGuzman adicionando PF também. O script completo está disponível. no meu primeiro post. Eu também coloquei o link no topo deste post.

--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

Qualquer sugestão, por favor. Obrigado pela ajuda.

Estou procurando ajuda para projeto de particionamento de tabelas para implementar no UAT e depois no PROD.

O processo atual é do SP inserindo dados da tabela de preparação para a tabela principal e atualizando a tabela com lógica de negócios e excluindo dados com mais de 90 dias. A tabela principal é uma tabela de 120 GB com 100 milhões de dados, a inserção, atualização e exclusão leva mais tempo, cerca de 1 hora para concluir o processo. Aqui eu postei --> Sugestão de lentidão do DML SP para carregamento de dados ETL

Este processo está sendo executado diariamente 4 vezes. Estou tentando testar e automatizar este processo com particionamento de tabela. O problema é que os dados são carregados diariamente 4 vezes e às vezes também executamos lotes extras com base na necessidade. Não consegui usar o particionamento diário ou mensal, pois não consigo alternar com os dados existentes. Acredito que preciso usar o particionamento por carga com base na data máxima atual. Alguém pode ajudar?

Não temos nenhum índice diferente da chave primária como índice clusterizado. Aqui está o teste e a amostra que testei.

Criação de tabela:

--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
)

Inserir amostra:

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

Próxima Inserção / Amostra de lote:

/*
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

Atualizar e Excluir:

--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 

Obrigado pela ajuda.

Testei esta partição mensal:

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]);

Editado: Tentei automatizar usando tempo máximo e +1 segundo etc., mas os dados, ao alternar, vão para limites de partição diferentes quando carrego 2 e 3 etc.

---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)

Como requisito para obter novos dados do editor para o assinante. Usei gerar um novo instantâneo, executar o leitor de log e executar o agente de distribuição.

Gostaria de saber qual opção usamos para inicializar o Assinante para todos os artigos. Posso ver as seguintes 4 opções.

A replicação está funcionando bem, mas ouvi da equipe de aplicativos que eles estão enfrentando algum problema ao comparar dados e carregar o trabalho ETL.

Usei drop and recreate e apareceu erro de relacionamento de chave primária e chave estrangeira. Então usei Truncar toda a tabela e carregar.

Esse é o método correto.