ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) não usa índice em (A,B,C)

Considere estas duas funções:

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

Tanto quanto eu entendo, eles produzem exatamente o mesmo resultado. Em outras palavras, a ordem em que você lista as colunas na PARTITION BYcláusula não importa.

Se houver um índice, (A,B,C)eu esperava que o otimizador usasse esse índice em ambas as variantes.

Mas, surpreendentemente, o otimizador decidiu fazer uma classificação extra explícita na segunda variante.

Eu vi isso no SQL Server 2008 Standard e no SQL Server 2014 Express.

Aqui está um script completo que usei para reproduzi-lo.

Tentei no Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 20 de fevereiro de 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1)

e Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) 27 de maio de 2016 15:33:17 Copyright (c) Microsoft Corporation Express Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pacote 1)

com o estimador de cardinalidade antigo e novo usando OPTION (QUERYTRACEON 9481)e OPTION (QUERYTRACEON 2312).

Configurar tabela, índice, dados de amostra

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] 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]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

Consultas

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

Planos de execução

PARTIÇÃO POR A,B

PARTIÇÃO POR B,A

Ambos

Como você pode ver, o segundo plano tem um Sort extra. Ordena por B,A,C. O otimizador, aparentemente, não é inteligente o suficiente para perceber que PARTITION BY B,Aé o mesmo PARTITION BY A,Be reclassificar os dados.

Curiosamente, a terceira consulta contém ambas as variantes ROW_NUMBERe não há classificação extra! O plano é o mesmo da primeira consulta. (O Projeto de Sequência tem expressão extra na Lista de Saída para a coluna extra, mas não Ordenação extra). Portanto, neste caso mais complicado, o otimizador parecia ser inteligente o suficiente para perceber que PARTITION BY B,Aé o mesmo que PARTITION BY A,B.

Na primeira e terceira consultas o operador Index Scan tem a propriedade Ordered:True, na segunda consulta é False.

Ainda mais interessante, se eu reescrever a terceira consulta assim (troque duas colunas):

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

então o Sort extra aparece novamente!

Alguém poderia dar uma luz? O que está acontecendo no otimizador aqui?