Hola, les paso la tabla y el proceso que toma registros
CREATE PROCEDURE [dbo].[_TEST] ( @Top int )
AS
BEGIN
DECLARE
@ERStatusPendingToCoordinate tinyint,
@ERStatusCoordinating tinyint,
@DateTimeToImages Datetime,
@DateTimeToCoordinate Datetime,
@ProcessID Smallint,
@UTCDate DateTime;
SET @ERStatusPendingToCoordinate = 1;
SET @ERStatusCoordinating = 4;
SET @ProcessID = @@SPID;
SET @UTCDate = GETUTCDate();
declare @Candidates table(EnforcementRecordID uniqueidentifier unique);
-- Seleccionamos una cantidad de registros que están disponibles para ser procesados
insert into @Candidates(EnforcementRecordID)
SELECT TOP (@Top) ER.EnforcementRecordID
FROM EnforcementRecords ER WITH (NOLOCK)
WHERE
ER.[Status] = @ERStatusPendingToCoordinate
AND ER.ProcessID IS NULL
ORDER BY
ER.[Datetime] ASC;
SET @UTCDate = GETUTCDate();
-- Ahora marcamos los registros que siguen disponibles para ser procesados, para indicar que están en procesamiento.
UPDATE EnforcementRecords
SET
ProcessID = @ProcessID,
StartProcessingDateTime = @UTCDate,
[Status]= @ERStatusCoordinating
OUTPUT
deleted.EnforcementRecordID
FROM EnforcementRecords ER
INNER JOIN @Candidates C ON ER.EnforcementRecordID = C.EnforcementRecordID
WHERE ER.ProcessID IS NULL;
END
----------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[EnforcementRecords](
[EnforcementRecordID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[DateTimeCreated] [datetime] NOT NULL,
[Status] [tinyint] NOT NULL,
[ProcessID] [smallint] NULL,
[StartProcessingDateTime] [datetime] NULL,
CONSTRAINT [PK_EnforcementRecords_ERID] PRIMARY KEY NONCLUSTERED
(
[EnforcementRecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DefaultIndexes]
) ON [TransactionData]
GO
CREATE NONCLUSTERED INDEX [IX_ProcessID_Status_DT_INC_ER] ON [dbo].[EnforcementRecords]
(
[ProcessID] ASC,
[Status] ASC,
[DateTime] ASC
)
INCLUDE ( [EnforcementRecordID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DefaultIndexes]
GO
Por otro lado hay un proceso que continuamente inserta registros en forma masiva pero siempre con fecha posterior a las que toma el SP.
Gracias
