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.
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]
Date
column often, you may want to consider clustering on theDate
column instead of theId
column.