Mitos e Verdades – ORDER BY + TOP/OFFSET/FOR XML dentro de Table Expressions

Fui questionado recentemente por um dos alunos de um curso que ministrei de TSQL sobre o porque de ser necessário o uso da cláusula TOP (n), OFFSET ou FOR XML PATH() dentro das expressões de tabela (VIEWs, TVFs, Derived Tables e CTEs) para que seja possível que o resultado execute com sucesso e não retorne erros.

Acredito que esse é um assunto interessante e decidi realizar alguns testes para entender melhor o funcionamento dessa issue dentro do SQL Server.


O TSQL é uma linguagem SET BASED (baseada em conjuntos – teoria Matemática dos Conjuntos). É importante entender que um conjunto, por sua natureza, não possui retorno ordenado.

Quando executado um comando de SELECT fazendo o uso da cláusula ORDER BY você demanda ao SQL Server uma das duas opções abaixo:

  1. Quero definir a ordenação das linhas que serão devolvidas através do comando de SELECT;
  2. Quero definir quais linhas serão devolvidas a partir de uma expressão TOP ou OFFSET;

É necessário entender também que as Expressões de Tabela (Table Expressions) nada mais fazem além de dar a uma consulta uma tabela virtual como origem (ou uma das origens). Essa tabela virtual será criada em tempo de execução através do comando de SELECT localizado dentro da Expressão de Tabela.

Isso quer dizer que o SQL Server irá primeiro realizar a consulta que está dentro da Query localizada na Table Expression e, a partir dessa tabela virtual que será gerada, iniciará a execução da query de fora da Table Expression (Outer query).

Dessa forma é possível entender o porquê de não podermos usar uma Cláusula ORDER BY dentro de uma Table Expression: O Retorno se torna não determinístico. O SQL Server não pode passar por cima da regra que define que um conjunto de OUTPUT não pode possuir ordenação.

Muitas pessoas acreditam que é possível “burlar” essa funcionalidade by design do SQL Server usando a cláusula TOP. Será possível que você veja abaixo quais as verdades e mentiras por detrás disso.


Use para os testes o banco de dados AdventureWorks2014.

Primeiramente veja o plano de execução estimado da query abaixo:

USE AdventureWorks2014
GO

SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

01

Observe que o operador SORT foi inserido no plano de execução. Isso quer dizer que o SQL Server está realizando a ordenação do retorno da consulta que fora executada.

Após isso, crie uma VIEW denominada [vw_Person_Names] utilizando a query abaixo e crie um plano de execução estimado para o comando SELECT após sua criação.

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names
AS
SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names

01

Observe que o plano de execução estimado será idêntico ao execution plan da query anterior. Isso ocorre porque o retorno do SELECT é determinístico graças ao comando TOP (n) na query. Dessa maneira, estamos usando a cláusula ORDER BY em uma de suas funções no TSQL: definir quais linhas serão devolvidas a partir de uma expressão TOP, OFFSET ou FOR XML PATH().

Neste caso, é possível usar corretamente o comando ORDER BY dentro de uma Table Expression – Seja ela uma VIEW, Table-Valued User-Definid Function (TVF), Common Table Expression (CTE) ou Derived Table.

É importante observar um outro ponto. Algumas vezes, quando o Query Optimizer decide usar um índice em cujo os registros já estão ordenados, o operador SORT não é usado na consulta, porém, se você selecionar o operador de Index Scan com o botão direito e for até a opção Properties, será possível observar que a opção Ordered será TRUE, ou seja, o retorno do operador de busca no index já está ordenado.

É o que será demonstrado no caso abaixo:

USE AdventureWorks2014
GO

SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY BusinessEntityID DESC

0001

Observe que o operador SORT não foi usado. Agora veja a aba de propriedades do operador Clustered Index Scan:

001


Agora você irá descobrir o outro lado da moeda acerca do ORDER BY sendo usado dentro de uma Expressão de Tabela que é quando você tentar usá-lo para definir qual a ordenação das linhas que serão devolvidas.

Use a query a baixo para realizar a análise do seu plano de execução:

USE AdventureWorks2014
GO

SELECT BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

02

Observe que o operador SORT foi novamente utilizado para realizar a ordenação do retorno da consulta acima.

Como já fora visto no início do post, se tentarmos usar essa mesma query dentro de uma Table Expression (uma view, por exemplo) um erro será retornado pois isso faria com que o SQL Server “fraudasse” uma das regras da teoria matemática dos conjuntos: Não há ordenação dos dados dentro de um SET.

Mas, é possível “burlar” este erro do SQL Server adicionando ao comando localizado dentro da VIEW a cláusula TOP 100 PERCENT. Veja:

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names2
AS 
SELECT TOP 100 PERCENT
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names2

A VIEW denominada [vw_Person_names2] foi criada com sucesso. Porém, observe um detalhe ao visualizar o plano de execução criado a partir do seu SELECT:

03

O operador SORT não é apresentado. Observe também abaixo na opção Ordered dentro das propriedades do operador Index Scan e veja que realmente a ordenação não ocorreu. O valor é FALSE:

04

Isso ocorre porque o SQL Server percebe que está ocorrendo através do comando TOP 100 PERCENT o retorno de todos os dados do conjunto e, por ser um retorno não determinístico, o Query Optmizer não pode desrespeitar a regra de não ordenação do conjunto.

Logo, ao fazer isso que foi demonstrado o retorno de sua Table Expression (neste caso da VIEW) não é ordenado mesmo com o comando ORDER BY estando presente na query. Veja abaixo:

06
Comando SELECT executado na VIEW.
05
Comando SELECT executado na Query.

Para contornar isso, algumas pessoas tentam novamente enganar o Query Optmizer usando o operador TOP (n) PERCENT onde (n) possui valor de 99.99. Isso quer dizer que você está pedindo ao SQL Server 99,99% dos dados do retorno.

Isso funcionará para forçar o SQL Server a trazer o resultado da consulta a partir de uma Table Expression ordenado, entretanto, isso pode gerar problemas.

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names3
AS 

SELECT TOP 99.99 PERCENT
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names3

Ao criar uma nova VIEW denominada [vw_Person_names3] através do comando acima fora executado um COUNT em ambos objetos (tabela [Person].[Person] e view [dbo].[vw_Person_names3]). Veja o resultado abaixo:

07

Ocorreu perda de registros (neste caso um somente) para o retorno em cuja query fora usado o TOP 99.99 PERCENT.


Por esses motivos, sempre que o resultado de uma Table Expression for não determinístico é recomentado a utilização da cláusula ORDER BY na query de fora da Table Expression (Outer query) evitando, dessa forma, resultados errôneos.

4 comentários sobre “Mitos e Verdades – ORDER BY + TOP/OFFSET/FOR XML dentro de Table Expressions

    1. Fico muito feliz a cada comentário teu, Fábio. Fico lisonjeado de receber esses elogios de alguem como você. Sim, irei escrever um livro até o meio do ano que vem. Está nos planos. Novamente, muito obrigado! 😀

      Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s