Avoid Sort operator in index plan of an update query which does not have an ORDER BY keyword

Question

I have an update query:-

Update PM.Contractual_Allowances  Set Provider_ID = 3  Where Tenant_ID = 1 and Carrier_ID = 203

For this above query I am getting execution plan as below :

I am trying to understand that why do I get the sort operator when I dont have an ORDER BY clause and what I can do to avoid it.

Below is the plan in text (aka 'Showplan_text'):

|--Sequence
   |--Index Update(OBJECT:([Ntier_Master].[PM].[Contractual_Allowances].[IX_Contractual_Allowances_Provider_ID_Tenant_ID]), SET:([Contractual_Allowance_ID1043] = [Ntier_Master].[PM].[Contractual_Allowances].[Contractual_Allowance_ID],[Provider_ID1044] = [Ntier_Master].[PM].[Contractual_Allowances].[Provider_ID],[Tenant_ID1045] = [Ntier_Master].[PM].[Contractual_Allowances].[Tenant_ID]) WITH ORDERED PREFETCH ACTION:([Act1042]))
   |    |--Sort(ORDER BY:([Ntier_Master].[PM].[Contractual_Allowances].[Provider_ID] ASC, [Ntier_Master].[PM].[Contractual_Allowances].[Tenant_ID] ASC, [Ntier_Master].[PM].[Contractual_Allowances].[Contractual_Allowance_ID] ASC, [Act1042] ASC))
   |         |--Filter(WHERE:(NOT [Expr1038]))
   |              |--Table Spool
   |                   |--Split
   |                        |--Clustered Index Update(OBJECT:([Ntier_Master].[PM].[Contractual_Allowances].[PK_Contractual_Allowances_Contractual_Allowance_ID]), SET:([Ntier_Master].[PM].[Contractual_Allowances].[Provider_ID] = [Expr1033],[Ntier_Master].[PM].[Contractual_Allowances].[Contractual_Allowance_TS] = [Expr1003]))
   |                             |--Compute Scalar(DEFINE:([Expr1038]=[Expr1038], [Expr1039]=[Expr1039]))
   |                                  |--Compute Scalar(DEFINE:([Expr1038]=CASE WHEN [Expr1007] THEN (1) ELSE (0) END, [Expr1039]=CASE WHEN [Expr1007] THEN (1) ELSE (0) END))
   |                                       |--Compute Scalar(DEFINE:([Expr1033]=(3)))
   |                                            |--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Ntier_Master].[PM].[Contractual_Allowances].[Provider_ID] = (3) THEN (1) ELSE (0) END))
   |                                                 |--Compute Scalar(DEFINE:([Expr1003]=gettimestamp((10))))
   |                                                      |--Clustered Index Scan(OBJECT:([Ntier_Master].[PM].[Contractual_Allowances].[PK_Contractual_Allowances_Contractual_Allowance_ID]),  WHERE:([Ntier_Master].[PM].[Contractual_Allowances].[Tenant_ID]=(1) AND [Ntier_Master].[PM].[Contractual_Allowances].[Carrier_ID]=(203)) ORDERED FORWARD)
   |--Index Update(OBJECT:([Ntier_Master].[PM].[Contractual_Allowances].[IX_Contractual_Allowances_Carrier_ID_Location_ID_Department_ID_Tenant_ID]), SET:([Contractual_Allowance_ID1046] = [Ntier_Master].[PM].[Contractual_Allowances].[Contractual_Allowance_ID],[Modifiers1047] = [Ntier_Master].[PM].[Contractual_Allowances].[Modifiers],[Carrier_ID1048] = [Ntier_Master].[PM].[Contractual_Allowances].[Carrier_ID],[Procedure_Code_ID1049] = [Ntier_Master].[PM].[Contractual_Allowances].[Procedure_Code_ID],[Location_ID1050] = [Ntier_Master].[PM].[Contractual_Allowances].[Location_ID],[Provider_ID1051] = [Ntier_Master].[PM].[Contractual_Allowances].[Provider_ID],[Department_ID1052] = [Ntier_Master].[PM].[Contractual_Allowances].[Department_ID],[Tenant_ID1053] = [Ntier_Master].[PM].[Contractual_Allowances].[Tenant_ID]) WITH ORDERED PREFETCH ACTION:([Act1042]))
        |--Sort(ORDER BY:([Ntier_Master].[PM].[Contractual_Allowances].[Carrier_ID] ASC, [Ntier_Master].[PM].[Contractual_Allowances].[Location_ID] ASC, [Ntier_Master].[PM].[Contractual_Allowances].[Department_ID] ASC, [Ntier_Master].[PM].[Contractual_Allowances].[Tenant_ID] ASC, [Ntier_Master].[PM].[Contractual_Allowances].[Contractual_Allowance_ID] ASC, [Act1042] ASC))
             |--Filter(WHERE:(NOT [Expr1039]))
                  |--Table Spool




 CREATE NONCLUSTERED INDEX IX_Contractual_Allowances_Location_ID_Tenant_ID ON PM.Contractual_Allowances (  Location_ID ASC  , Tenant_ID ASC  )   WITH (  PAD_INDEX =  OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Contractual_Allowances_Provider_ID_Tenant_ID ON PM.Contractual_Allowances (  Provider_ID ASC  , Tenant_ID ASC  )   WITH (  PAD_INDEX =  OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Contractual_Allowances_Carrier_ID_Current_Effective_Date_Tenant_ID ON PM.Contractual_Allowances (  Carrier_ID ASC  , Current_Effective_Date ASC  , Tenant_ID ASC  )   WITH (  PAD_INDEX =  OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
 CREATE NONCLUSTERED INDEX IX_Contractual_Allowances_Carrier_ID_Location_ID_Department_ID_Tenant_ID ON PM.Contractual_Allowances (  Carrier_ID ASC  , Location_ID ASC  , Department_ID ASC  , Tenant_ID ASC  )   INCLUDE ( Modifiers , Procedure_Code_ID , Provider_ID )  WITH (  PAD_INDEX =  OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
CREATE NONCLUSTERED INDEX IX_Contractual_Allowances_Procedure_Code_ID_Tenant_ID ON PM.Contractual_Allowances (  Procedure_Code_ID ASC  , Tenant_ID ASC  )   WITH (  PAD_INDEX =  OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 

Answer 1

ORDER BY clauses are not the only thing that cause a sort. GROUP BY, DISTINCT, window ranking functions (e.g. ROW_NUMBER, RANK), window aggregate functions (e.g. SUM() OVER (PARTITION BY ...), window frame functions (e.g. LAG and LEAD) are among many things that cause a sort.

When we add indexes we're pre-sorting data so that it does not need to be sorted when queried. Sorts in the exectution plan mean that (1) there was not an index available to handle the sort or (2) the was an index that could handle the sort but the optimizer chose not to use it. Consider the following sample data:

if object_id('tempdb..#sometable') is not null drop table #sometable;
create table #sometable (col1 int, col2 int);
insert #sometable values(1,10),(1,20),(2,15),(2,50),(3,10);

Next, run these queries with "Include Actual Execution Plan" turned on.

select col1, max(col2)
from #sometable
group by col1;

select distinct col1
from #sometable;

select col1, col2, avg(col2) over (partition by col1)
from #sometable;

select col1, col2, avg(col2) over (partition by col1 order by (select null))
from #sometable;

Note the execution plans:

You can get more info about what was sorted by holding the mouse over the sort operator:

Here it's it needs to sort col1 so let's add this index, run the queries and examine the execution plan:

--alter table #sometable
create clustered index uq_sometable on #sometable(col1);

Now the new execution plans:

As you can see, the sorts are all gone. There's so much more to this topic but hopefully this helps you understand how you can get sorts without an ORDER BY and how to get rid of them.