Sort Operation with Covering Index-CodePudding

We have a query producing a Sort operation w/in the plan but we have a covering index:

I was under the impression that a covering index would help mitigate the need for a Sort operation.

Query plan: https://www.brentozar.com/pastetheplan/?id=By-AXqg2K

The POSTAL_CODE table:

CREATE TABLE [dbo].[POSTAL_CODE](
    [PC_VALUE] [nvarchar](6) NOT NULL,
    [PC_CITY] [nvarchar](50) NULL,
    [PC_SP_ABBREV] [nvarchar](3) NULL,
    [PC_COU_ABBREV] [nvarchar](2) NOT NULL,
    [PC_LATITUDE] [real] NULL,
    [PC_LONGITUDE] [real] NULL,
    [PC_TZ_ID] [int] NULL,
    [PC_OBSERVES_DAYLIGHT] [nchar](1) NULL,
    [PC_COUNTY] [nvarchar](50) NULL,
    [PC_DATE_INSERTED] [datetime] NOT NULL,
    [PC_DATE_UPDATED] [datetime] NOT NULL,
    [PC_STATUS] [nchar](1) NOT NULL,
    [PC_ZON_ID] [int] NULL,
    [PC_AREA_CODE] [nvarchar](3) NULL,
    [PC_PREFERRED] [nchar](1) NOT NULL,
    [PC_CLU_ID] [int] NULL,
    [PC_WORK_INDEX] [tinyint] NULL,
    [PC_WORK_MINS_DIFF] [smallint] NULL,
    [PC_LIS_COUNT] [smallint] NULL,
    [PC_WORK_MINS_AVG] [smallint] NULL,
 CONSTRAINT [POSTAL_CODE_POSTAL_CODE$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [PC_VALUE] ASC,
    [PC_COU_ABBREV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

CodePudding user response：

There is no way for the server to use this index for your query, because the ordering criterion is a complex calculation, which means that the result is not going to be the same sort order as PC_VALUE.

One option is to create a computed column on that calculation, then index it

ALTER TABLE POSTAL_CODE
  ADD Distance AS (
    3958 *
    3.1415926 *
    SQRT(
        (PC_LATITUDE - 1) * (PC_LATITUDE - 1)  
        COS(PC_LATITUDE / 57.29578) *
        COS(1 / 57.29578) *
        (PC_LONGITUDE - 1) * (PC_LONGITUDE - 1)
    ) /
    180)
  PERSISTED;  -- must be persisted because it is an imprecise (float) value
    
CREATE NONCLUSTERED INDEX IX ON POSTAL_CODE(Distance) INCLUDE (PC_VALUE);

Now you will get no sort, as shown in this fiddle. The server can simply look at the query and say "We have an index on this value already, let's look up the first row in the index".

You can obviously also now refer to just that computed column:

SELECT TOP 1
  PC_VALUE
FROM POSTAL_CODE PC
ORDER BY Distance;

But I agree with commenters that you should probably consider geography and a spatial index instead.