primary key – Igor Micev

Why you should not update Primary Key values

Posted on November 11, 2015 by Igor Micev — No Comments ↓

Recently I had the chance to take a look at a problematic query in an application. I caught the query in profiler and it’s the following:


SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Modify the updatable columns
UPDATE [dbo].[StationaryMaster] 
SET 
	[Id] = @Id, --comment this
    [HistoryId] = @HistoryId,
    [ServiceId] = @ServiceId,
    [ServiceName] = @ServiceName,
    [Status] = @Status,
    [OrderId] = @OrderId,
    [DoctorId] = @DoctorId,
    [TransferedFromDoctorId] = @TransferedFromDoctorId,
    [StationaryNumber] = @StationaryNumber,
    [StationaryYear] = @StationaryYear,
    [Protokol] = @Protokol,
    [Date] = @Date,
    [CreatedBy] = @CreatedBy,
    [ModifiedBy] = @ModifiedBy,
    [CreatedDate] = @CreatedDate,
    [ModifiedDate] = @ModifiedDate
WHERE 
      [Id] = @OriginalId

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Modify the updatable columns

UPDATE [dbo].[StationaryMaster]

SET

[Id] = @Id, --comment this

[HistoryId] = @HistoryId,

[ServiceId] = @ServiceId,

[ServiceName] = @ServiceName,

[Status] = @Status,

[OrderId] = @OrderId,

[DoctorId] = @DoctorId,

[TransferedFromDoctorId] = @TransferedFromDoctorId,

[StationaryNumber] = @StationaryNumber,

[StationaryYear] = @StationaryYear,

[Protokol] = @Protokol,

[Date] = @Date,

[CreatedBy] = @CreatedBy,

[ModifiedBy] = @ModifiedBy,

[CreatedDate] = @CreatedDate,

[ModifiedDate] = @ModifiedDate

WHERE

[Id] = @OriginalId

The query was throwing the following error: The query processor ran out of Continue reading Why you should not update Primary Key values→