0

I'm working on enviorment SQL azure.

I've got a stored procedure that runs from several AKS simultaneously and insert a message.

So it's a race for who writes the message first and the rest fails. The insert fails due to Unique index and send the Id of the failed message to the service.

here is the stored procedure:

CREATE OR ALTER PROCEDURE Saga.AddMessage
(
    @SagaIdentity NVARCHAR(256),
    @MessageId NVARCHAR(64),
    @SagaMessage NVARCHAR(MAX),
    @Created DATETIME2(7),
    @Updated DATETIME2(7),
    @SubscriptionStatus SMALLINT
)
AS
BEGIN
    BEGIN TRY
        INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])
        SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus        
        WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId);
    
        SELECT [Id] AS Results
        FROM [Saga].[SubscribersMessages] 
        WHERE MessageId = @MessageId
    END TRY
    BEGIN CATCH
        SELECT [Id] AS Results
            FROM [Saga].[SubscribersMessages] 
            WHERE MessageId = @MessageId
    END CATCH;
END

and here is the unique index :

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Saga.SubscribersMessages','U') AND name = 'UX_SubscribersMessages_MessageId')
BEGIN
    CREATE UNIQUE INDEX UX_SubscribersMessages_MessageId
    ON [Saga].[SubscribersMessages] (MessageId);
END

I have a couple of problems with this service:

  1. Peformance issue - I don't like the fact the the process rely CATCH on failed processed to return the Id.

  2. I want to Partition the table using the Created field in order to truncate partition in the future and i get an error - Column 'Created' is partitioning column of the index 'UX_SubscribersMessages_MessageId'. Partition columns for a unique index must be a subset of the index key I thought about the MessageId as my Primary Key as it is unique but there are downsides to that as the MessageId is NVARCHAR(64).

Here is the execution plan:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.588" Build="16.0.5506.10218" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0432927" StatementText="INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])&#xD;&#xA;      SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus        &#xD;&#xA;      WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId)" StatementType="INSERT" QueryHash="0x9495ED884EEA5C34" QueryPlanHash="0xB362830815E8CA5B" RetrievedFromCache="true" StatementSqlHandle="0x0900576108E5BB43DB4B85D93C51904339770000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="814625945" StatementParameterizationType="0" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="432">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="566169" EstimatedPagesCached="70771" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2918912" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[55a15ae9-f6f0-4e08-8548-21516f68285e]" Schema="[Saga]" Table="[SubscribersMessages]" Statistics="[UX_SubscribersMessages_MessageId]" ModificationCount="68591" SamplingPercent="0.928381" LastUpdate="2024-05-07T07:13:48.34" />
            </OptimizerStatsUsage>
            <WaitStats>
              <Wait WaitType="PREEMPTIVE_HTTP_REQUEST" WaitTimeMs="15" WaitCount="3" />
              <Wait WaitType="PAGEIOLATCH_EX" WaitTimeMs="9" WaitCount="1" />
              <Wait WaitType="LOGMGR_FLUSH" WaitTimeMs="4" WaitCount="1" />
            </WaitStats>
            <QueryTimeStats CpuTime="0" ElapsedTime="30" />
            <RelOp AvgRowSize="9" EstimateCPU="4E-06" EstimateIO="0.04" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="0.0432927">
              <OutputList />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="30" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <Update DMLRequestSort="false">
                <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[Saga_SubscribersMessages]" IndexKind="Clustered" Storage="RowStore" />
                <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
                <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[IX_SubscribersMessages_SagaIdentity]" IndexKind="NonClustered" Storage="RowStore" />
                <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[IX_SubscribersMessages_SubscriptionStatus]" IndexKind="NonClustered" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SagaIdentity] = RaiseIfNullInsert([@SagaIdentity]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[MessageId] = RaiseIfNullInsert([@MessageId]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SagaMessage] = RaiseIfNullInsert([@SagaMessage]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Created] = RaiseIfNullInsert([@Created]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Updated] = [Expr1006],[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SubscriptionStatus] = RaiseIfNullInsert([@SubscriptionStatus]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Id] = [Expr1005]">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SagaIdentity" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullInsert">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="@SagaIdentity" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullInsert">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="@MessageId" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SagaMessage" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullInsert">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="@SagaMessage" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Created" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullInsert">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="@Created" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Updated" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1006" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SubscriptionStatus" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullInsert">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="@SubscriptionStatus" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1005" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00328874">
                  <OutputList>
                    <ColumnReference Column="Expr1005" />
                    <ColumnReference Column="Expr1006" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
                  </RunTimeInformation>
                  <Top RowCount="false" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(1)">
                        <Const ConstValue="(1)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328864">
                      <OutputList>
                        <ColumnReference Column="Expr1005" />
                        <ColumnReference Column="Expr1006" />
                      </OutputList>
                      <ComputeScalar>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1006" />
                            <ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(7),getdate(),0)">
                              <Identifier>
                                <ColumnReference Column="ConstExpr1007">
                                  <ScalarOperator>
                                    <Convert DataType="datetime2" Scale="7" Style="0" Implicit="true">
                                      <ScalarOperator>
                                        <Intrinsic FunctionName="getdate" />
                                      </ScalarOperator>
                                    </Convert>
                                  </ScalarOperator>
                                </ColumnReference>
                              </Identifier>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328854">
                          <OutputList>
                            <ColumnReference Column="Expr1005" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
                          </RunTimeInformation>
                          <ComputeScalar ComputeSequence="true">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1005" />
                                <ScalarOperator ScalarString="getidentity((750625717),(22),NULL)">
                                  <Intrinsic FunctionName="getidentity">
                                    <ScalarOperator>
                                      <Const ConstValue="(750625717)" />
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="(22)" />
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="NULL" />
                                    </ScalarOperator>
                                  </Intrinsic>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Anti Semi Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00328844">
                              <OutputList />
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
                              </RunTimeInformation>
                              <NestedLoops Optimized="false">
                                <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="5" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
                                  <OutputList />
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                                  </RunTimeInformation>
                                  <ConstantScan />
                                </RelOp>
                                <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16349100">
                                  <OutputList />
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="1" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues />
                                    <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[@MessageId]">
                                                <Identifier>
                                                  <ColumnReference Column="@MessageId" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                              </NestedLoops>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                      </ComputeScalar>
                    </RelOp>
                  </Top>
                </RelOp>
              </Update>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@SubscriptionStatus" ParameterDataType="smallint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@Created" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2024-05-07 08:08:32.7700000'" ParameterRuntimeValue="'2024-05-07 08:08:32.7700000'" />
              <ColumnReference Column="@SagaMessage" ParameterDataType="nvarchar(max)" ParameterCompiledValue="N'{&quot;RequestId&quot;:&quot;ef48ce6c-201e-4c88-a823-cb43cb8cb467&quot;,&quot;CorrelationId&quot;:&quot;f519d43d-7a8b-4833-b393-ab96fe85b0ff&quot;,&quot;Created&quot;:&quot;2023-11-21T14:20:09.9658724Z&quot;}'" ParameterRuntimeValue="N'{&quot;RequestId&quot;:&quot;ef48ce6c-201e-4c88-a823-cb43cb8cb467&quot;,&quot;CorrelationId&quot;:&quot;f519d43d-7a8b-4833-b393-ab96fe85b0ff&quot;,&quot;Created&quot;:&quot;2023-11-21T14:20:09.9658724Z&quot;}'" />
              <ColumnReference Column="@SagaIdentity" ParameterDataType="nvarchar(256)" ParameterCompiledValue="N'40677e00-5528-4310-bcd1-d1ed19b11758'" ParameterRuntimeValue="N'40677e00-5528-4310-bcd1-d1ed19b11758'" />
              <ColumnReference Column="@MessageId" ParameterDataType="nvarchar(64)" ParameterCompiledValue="N'586772d1101b432390126954ced52ed6'" ParameterRuntimeValue="N'586772d1101b432390126954ced52ed6'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="2" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0032831" StatementText="SELECT [Id] AS Results&#xD;&#xA;      FROM [Saga].[SubscribersMessages] &#xD;&#xA;        WHERE MessageId = @MessageId" StatementType="SELECT" QueryHash="0x65EB6536A27948CF" QueryPlanHash="0x79E4B69DD48411A3" RetrievedFromCache="true" StatementSqlHandle="0x09001BDE820A3F9CBFDE19FD57F0EBDA0E040000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="814625945" StatementParameterizationType="0" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="9" CompileCPU="0" CompileMemory="288">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="566169" EstimatedPagesCached="70771" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2918912" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[55a15ae9-f6f0-4e08-8548-21516f68285e]" Schema="[Saga]" Table="[SubscribersMessages]" Statistics="[UX_SubscribersMessages_MessageId]" ModificationCount="68591" SamplingPercent="0.928381" LastUpdate="2024-05-07T07:13:48.34" />
            </OptimizerStatsUsage>
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16349100">
              <OutputList>
                <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
                <SeekPredicates>
                  <SeekPredicateNew>
                    <SeekKeys>
                      <Prefix ScanType="EQ">
                        <RangeColumns>
                          <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
                        </RangeColumns>
                        <RangeExpressions>
                          <ScalarOperator ScalarString="[@MessageId]">
                            <Identifier>
                              <ColumnReference Column="@MessageId" />
                            </Identifier>
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>
                </SeekPredicates>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@MessageId" ParameterDataType="nvarchar(64)" ParameterCompiledValue="N'586772d1101b432390126954ced52ed6'" ParameterRuntimeValue="N'586772d1101b432390126954ced52ed6'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
Improve this question

2 Answers 2

Reset to default
2

You need to add locking hints to avoid constraint errors. Otherwise what can happen is that the server checks the table, then releases the lock before inserting, allowing another session to jump in. (In SNAPSHOT mode it won't even see the other session).

You can also use an OUTPUT clause, then do a conditional SELECT if there was no results.

CREATE OR ALTER PROCEDURE Saga.AddMessage
(
    @SagaIdentity NVARCHAR(256),
    @MessageId NVARCHAR(64),
    @SagaMessage NVARCHAR(MAX),
    @Created DATETIME2(7),
    @Updated DATETIME2(7),
    @SubscriptionStatus SMALLINT
)
AS

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

INSERT INTO Saga.SubscribersMessages
  (SagaIdentity, MessageId, SagaMessage, Created, Updated, SubscriptionStatus)
  OUTPUT inserted.Id AS Results
SELECT
  @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
WHERE NOT EXISTS (SELECT 1
    FROM Saga.SubscribersMessages sm WITH (UPDLOCK, HOLDLOCK)
    WHERE sm.MessageId = @MessageId
);


IF @@ROWCOUNT = 0    
    SELECT sm.Id AS Results
    FROM Saga.SubscribersMessages sm
    WHERE sm.MessageId = @MessageId;

COMMIT;

Alternatively

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

DECLARE @existingId bigint;

SELECT @existingId = sm.Id
FROM Saga.SubscribersMessages sm WITH (UPDLOCK, HOLDLOCK)
WHERE sm.MessageId = @MessageId;

IF @existingId IS NULL
BEGIN
    INSERT INTO Saga.SubscribersMessages
      (SagaIdentity, MessageId, SagaMessage, Created, Updated, SubscriptionStatus)
      OUTPUT inserted.Id AS Results
    SELECT
      @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
END
ELSE
BEGIN
    SELECT @existingId AS Results;
END;

COMMIT;

Do yourself a favour and don't quote object and column names unless absolutely necessary, it's really annoying to read.

Improve this answer
1

Avoiding TRY...CATCH

Another option is to alter the existing unique index to silently reject duplicates:

ALTER INDEX UX_SubscribersMessages_MessageId 
    ON Saga.SubscribersMessages
    SET 
    (
        IGNORE_DUP_KEY = ON 
        (
            SUPPRESS_MESSAGES = ON
        )
    );

(note: MERGE statements do not respect IGNORE_DUP_KEY)

Your procedure can now be as simple as:

CREATE OR ALTER PROCEDURE Saga.AddMessage
    @SagaIdentity nvarchar(256),
    @MessageId nvarchar(64),
    @SagaMessage nvarchar(max),
    @Created datetime2(7),
    @Updated datetime2(7),
    @Subscriptionstatus smallint
AS
SET XACT_ABORT, NOCOUNT ON;

-- UX_SubscribersMessages_MessageId has IGNORE_DUP_KEY
INSERT Saga.SubscribersMessages
(
    SagaIdentity, 
    MessageId, 
    SagaMessage, 
    Created, 
    Updated, 
    SubscriptionStatus
)
VALUES
(
    @SagaIdentity, 
    @MessageId, 
    @SagaMessage, 
    @Created, 
    SYSUTCDATETIME(), 
    @SubscriptionStatus
);

SELECT Results = SM.Id
FROM Saga.SubscribersMessages AS SM
WHERE SM.MessageId = @MessageId;

SQL Server internally takes care of the necessary UPDLOCK and SERIALIZABLE hints for you, and you don't have to write an EXISTS clause. Using this approach does make the code less explicit though (hence my code comment).

Partitioning and Uniqueness

There's no perfect answer to this.

If you want to partition a unique index (including a primary key), it must have the partitioning column as part of the key.

Your unique index would have to be on (MessageId, Created) to be partitioned by Created. This means the same MessageId could be present more than once, so long as the Created values are different. Maybe that's acceptable in your system, maybe it isn't.

Alternatively, you could retain the unique index as it is (by specifying ON [filegroup] instead of ON [partition_scheme]), but that would result in an unaligned index (that is, one not partitioned on an equivalent scheme to the base table).

Having an unaligned index disallows partition switching and TRUNCATE TABLE WITH PARTITIONS, which is something you say you want. You could work around that by dropping the unaligned index before the truncation and rebuilding it afterwards, but that's hardly convenient or free.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.