Blog – Fabiano Neves Amorim

Just created a new connect item about a bug on Query Optimizer, I appreciate if you could vote 😉

More details here: https://connect.microsoft.com/SQLServer/feedback/details/679342/unnecessary-sort#details

IF OBJECT_ID('Departamentos') IS NOT NULL
  DROP TABLE Departamentos
GO
CREATE TABLE Departamentos (ID      Int IDENTITY(1,1) PRIMARY KEY,
                            Nome_Dep VarChar(200))
GO

INSERT INTO Departamentos(Nome_Dep) 
VALUES('Vendas'), ('TI'), ('Recursos Humanos')
GO

IF OBJECT_ID('Funcionarios') IS NOT NULL
  DROP TABLE Funcionarios
GO
CREATE TABLE Funcionarios (ID      Int IDENTITY(1,1) PRIMARY KEY,
                           ID_Dep  Int,
                           Nome    VarChar(200),
                           Salario Numeric(18,2))
GO

INSERT INTO Funcionarios (ID_Dep, Nome, Salario) 
VALUES(1, 'Fabiano', 2000), (1, 'Amorim', 2500), (1, 'Diego', 9000),
      (2, 'Felipe', 2000), (2, 'Ferreira', 2500), (2, 'Nogare', 11999),
      (3, 'Laerte', 5000), (3, 'Luciano', 23500), (3, 'Zavaschi', 13999)
GO

SELECT Departamentos.Nome_Dep,
       Nome,
       Salario,
       avg(Salario) OVER (PARTITION BY Departamentos.Nome_Dep) AS AvgSalario,
       rank() OVER (PARTITION BY Departamentos.Nome_Dep ORDER BY Salario desc) AS RankSalario
  FROM Funcionarios 
 INNER JOIN Departamentos
    ON Funcionarios.ID_Dep = Departamentos.ID
OPTION (RECOMPILE)
GO
Two Sorts:

SELECT Departamentos.Nome_Dep,
       Nome,       
       Salario,
       rank() OVER (PARTITION BY Departamentos.Nome_Dep ORDER BY Salario desc) AS RankSalario,
       avg(Salario) OVER (PARTITION BY Departamentos.Nome_Dep) AS AvgSalario
  FROM Funcionarios 
 INNER JOIN Departamentos
    ON Funcionarios.ID_Dep = Departamentos.ID
OPTION (RECOMPILE)
GO

One Sort: