3

I am running the following query on a database with 7.5 million records:

UPDATE TestTable
SET TestCol='1234', TestCol2='1234', TestCol3='1234', Anonymised=1
WHERE [Date] >= '25 June 2016' 
AND [Date] <= '25 August 2016'

For some reason the execution plan includes a sort operation on the primary key.

enter image description here enter image description here

I wouldn't expect it to have to sort the primary key given that there is already a clustered index on that column.

Can anyone shed some light on why this is happening and what I might do to avoid the sort operation?

Edit:

Table creation script

CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestCol] [nvarchar](50) NOT NULL,
    [TestCol2] [nvarchar](50) NOT NULL,
    [TestCol3] [nvarchar](50) NOT NULL,
    [Anonymised] [tinyint] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
1
  • If you do range queries on the Date column often, you may want to consider clustering on the Date column instead of the Id column. Commented Jun 26, 2018 at 2:33

1 Answer 1

10

Your plan is using a nonclustered index keyed first on, presumably, Date to find values that qualify for your WHERE clause.

In order to optimize IO access of the clustered index update, data coming out of that index access operation is sorted to match key order of the PK/CX.

As an aside, you shouldn't be updating that many rows in one go.

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.