Piores práticas – Utilizar a cláusula ORDER BY em Views

Bom Dia Pessoal,

Hoje vou falar sobre um péssimo hábito que alguns desenvolvedores e DBAs praticam de vez em quando (sobretudo os que só utilizam ferramentas gráficas) que é o de colocar a cláusula ORDER BY em Views. Não posso dizer que nunca fiz isso (mas já faz muito tempo) e que certamente alguns dos que lêem esse artigo também o fizeram (ou ainda fazem). O que há de tão ruim em utilizar uma cláusula ORDER BY em uma View ? Não se pode desejar obter o resultado já ordenado ? Não posso deixar de reconhecer que isso facilita em alguns aspectos (sobretudo os de formatação), mas nada mais justo de explicar o porquê de caracterizar a ordenação de dados em Views como uma pior prática.

Antes de iniciar, como é de praxe, um script pode dizer mais que mil palavras. O script abaixo cria uma tabela com vários dados nos quais as explicações serão baseadas:

— Cria uma tabela de clientes com cerca de 10.000 linhas
CREATE TABLE tblClientes (
    ID INT IDENTITY(1,1), Nome VARCHAR(50),
    NomeDoMeio VARCHAR(50), SobreNome VARCHAR(50),
    CPF CHAR(11), DataCad SMALLDATETIME, CEP CHAR(8))

— Cria tabelas auxiliares para ajudar a "gerar" os registros
DECLARE @Nome TABLE (Nome VARCHAR(50))
DECLARE @NomeDoMeio TABLE (NomeDoMeio VARCHAR(50))
DECLARE @SobreNome TABLE (SobreNome VARCHAR(50))

— Insere os registros necessários para "gerar" os Clientes
INSERT INTO @Nome VALUES (‘Bárbara’)
INSERT INTO @Nome VALUES (‘Íris’)
INSERT INTO @Nome VALUES (‘Davi’)
INSERT INTO @Nome VALUES (‘Bia’)
INSERT INTO @Nome VALUES (‘Gabriela’)
INSERT INTO @Nome VALUES (‘Gonçalo’)
INSERT INTO @Nome VALUES (‘Caio’)
INSERT INTO @Nome VALUES (‘Yasmin’)
INSERT INTO @Nome VALUES (‘Aline’)
INSERT INTO @Nome VALUES (‘Larissa’)

INSERT INTO @NomeDoMeio VALUES (‘Bernado’)
INSERT INTO @NomeDoMeio VALUES (‘Prado’)
INSERT INTO @NomeDoMeio VALUES (‘Sallas’)
INSERT INTO @NomeDoMeio VALUES (‘Romualdo’)
INSERT INTO @NomeDoMeio VALUES (‘Tuma’)
INSERT INTO @NomeDoMeio VALUES (‘Antunes’)
INSERT INTO @NomeDoMeio VALUES (‘Ribeiro’)
INSERT INTO @NomeDoMeio VALUES (‘Rodrigues’)
INSERT INTO @NomeDoMeio VALUES (‘Macedo’)
INSERT INTO @NomeDoMeio VALUES (‘Mendes’)

INSERT INTO @SobreNome VALUES (‘Pereira’)
INSERT INTO @SobreNome VALUES (‘Silva’)
INSERT INTO @SobreNome VALUES (‘Souza’)
INSERT INTO @SobreNome VALUES (‘Gonçalves’)
INSERT INTO @SobreNome VALUES (‘Costa’)
INSERT INTO @SobreNome VALUES (‘Garcia’)
INSERT INTO @SobreNome VALUES (‘Ferreira’)
INSERT INTO @SobreNome VALUES (‘Matarazzo’)
INSERT INTO @SobreNome VALUES (‘Melo’)
INSERT INTO @SobreNome VALUES (‘Gomes’)

— Gera os registros de clientes
INSERT INTO tblClientes (Nome, NomeDoMeio, SobreNome)
SELECT Nome, NomeDoMeio, SobreNome
FROM @Nome CROSS JOIN @NomeDoMeio CROSS JOIN @SobreNome
ORDER BY NewID()

— Popula os demais campos de clientes de forma aleatória
UPDATE tblClientes SET
    DataCad = DateADD(DD,-ABS(CHECKSUM(NewID())) / 100000,GETDATE()),
    CPF = CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10),
    CEP = CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10)

O script acima produziu uma tabela de 1000 clientes com registros e colunas completamente aleatórios. O primeiro passo é tentar criar uma View ordenada sobre os registros em questão através do script abaixo:

CREATE VIEW vClientes
AS
SELECT
    Nome + ‘ ‘ + NomeDoMeio + ‘ ‘ + SobreNome AS NomeCompleto,
    CPF, DataCad, CEP FROM tblClientes
ORDER BY NomeCompleto

Embora o SELECT não tenha absolutamente nada de errado (basta executá-lo separadamente), ao executar o script de criação da View, um erro é retornado

Msg 1033, Level 15, State 1, Procedure vClientes, Line 7
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

A mensagem de erro é bem clara e a cláusula ORDER BY não pode ser utilizada em Views, InLine Functions, Derived Tables, Subqueries e CTEs a menos que a cláusula TOP ou FOR XML seja especificada. Essa clara limitação (não é exclusiva do SQL Server) impede que Views possam retornar seus dados de forma ordenada.

Um pouco de conceitos SQL ANSI

A SQL é uma linguagem que atua sobre conjuntos e não sobre linhas. Ela preocupa-se em recuperar e gravar conjuntos sem fazer qualquer distinção entre linhas individualmente. Claro que é possível recuperar um conjunto de uma linha ou ainda gravar um conjunto de uma linha, mas mesmo nesse caso ainda se trabalha em conjuntos. A manipulação individual de registros não é prerrogativa da SQL. Para esta, não há nenhum sentido em manter a ordem de um conjunto de registros ou ainda conhecer a posição individual de cada linha. Uma consulta como "Recupere todos os clientes cujo nome do meio seja Mendes" trará sempre o mesmo resultado independente desse resultado estar ordenado por CPF ou qualquer outra coluna. Não faria sentido por exemplo efetuar um JOIN entre os "clientes ordenados por CPF" com qualquer outra tabela. Bastaria apenas fazer o JOIN de clientes com outra tabela, pois, a ordem não afetaria os clientes participantes do JOIN e nem o seu resultado.

A única situação que a ordem importa, ou melhor dizendo, que ela é necessária são situações que envolvam o ranqueamento. Se desejarmos obter os 10 clientes que mais compraram no ano de 2008 é necessário utilizar o ORDER BY sobre uma possível coluna de total ou quantidade. Essa coluna é fundamental para descobrir que são os 10 clientes que mais compraram e nesse caso, a ausência do ORDER BY poderá mudar o resultado da consulta. Para essa situação o ORDER BY se faz necessário, mas não por uma questão estética, mas sim para garantia que os 10 clientes que mais compraram em 2008 são aqueles recuperados por ordem decrescente de total ou quantidade (TOP 10 e ORDER BY Total DESC).

O "erro" das ferramentas gráficas

Ferramentas gráficas como o Enterprise Manager e o SQL Server Management Studio facilitam o desenvolvimento de código de Views introduzindo alguns assistentes para criá-las. Basta apenas escolher as tabelas (opcionalmente realizar as junções desejadas), colunas, expressões calculadas, etc. Nesses assistentes há a possibilidade de utilizar ordenação visto que conforme descrito anteriormente, em situações de ranqueamento ela é necessária. O erro desses assistentes é dar a impressão ao usuário de que os dados podem ser ordenados por uma questão meramente estética ou facilitar sua apresentação. Para não violar as regras de criação de Views, normalmente os assistentes utilizam o artifício do TOP. Se a View apresentada fosse criada por um desses assistentes, possivelmente teria o código parecido com o abaixo:

CREATE VIEW vClientes
AS
SELECT TOP
100 PERCENT
    Nome + ‘ ‘ + NomeDoMeio + ‘ ‘ + SobreNome AS NomeCompleto,
    CPF, DataCad, CEP FROM tblClientes
ORDER BY NomeCompleto

Conseqüências no SQL Server 2000

Se você estiver utilizando o SQL Server 2000 e emitir um comando de SELECT contra a View vClientes, poderemos visualizar o plano de execução dessa consulta onde a ordenação é bem clara.

O SELECT na View vClientes produziu um plano que leu os 1.000 registros da tabela tblClientes e os ordenou com base no nome completo. É interessante perceber que se a consulta tem um custo total de 100%, cerca de 43% desse esforço foi apenas para ordenar os resultados. Isso mostra o quanto o custo de ordenação (SORT) pode ser oneroso.

Não haveria a menor diferença entre colocar o ORDER BY na View ou retirá-lo da View e colocá-lo no SELECT. Se o ORDER BY é realmente necessário ele terá que ser contemplado quer seja dentro do código da View ou na instrução SELECT feita contra a View. O problema de embutir o ORDER BY dentro da View é que os seus resultados sempre serão ordenados pelas colunas especificadas na cláusula ORDER BY. Isso é prejudicial, pois, caso alguém solicite os dados da View, mas queira alterar a ordem colocando um outro ORDER BY, haverá um duplo SORT. Ex:

SELECT * FROM vClientes ORDER BY DataCad

Como a View ordena por NomeCompleto e a instrução de SELECT ordena por DataCad, haverá a ordenação por NomeCompleto e em seguida a ordenação por DataCad produzindo o plano abaixo:

Se for observada a instrução SELECT, no final das contas, o que importa é a ordenação final (DataCad). Como a ordenação por nome completo está embutida na View, ela também foi realizada, mas na prática seus efeitos foram desprezados, pois, ao final os resultados são exibidos com base na ordem de data de cadastro (DataCad).

Esse plano mostra que a ordenação embutida dentro de uma view é prejudicial, pois, pode aumentar a ocorrência de um duplo SORT e como sabemos o custo de ordenação é elevado. No exemplo em questão, a ordenação por nome completo tem um custo de 33% do resultado da consulta e esse custo é literalmente inútil. Essa consulta poderia ser feita gastando-se 67% dos recursos se o ORDER BY não estivesse na View. Vale a pena lembrar que esse custo inútil de ordenação também terá impactos toda vez que a tabela for utilizada em JOINs. Pode ser que o JOIN recebe os dados em uma certa ordem, mas opte por atribuir uma outra ordem mais interessante para a junção. Nesse caso teríamos outra situação de duplo SORT.

Conseqüências para o SQL Server 2005 e 2008

Se você estiver utilizando o SQL Server 2005 ou 2008, terá uma surpresa ao executar uma consulta contra a View. É nítido que a ordem dos registros com base no nome completo não é respeitada mesmo que a View tenha forçado essa ordenação. Isso ocorre porque o otimizador é esperto o suficiente para perceber que um código do tipo TOP 100 PERCENT representa todos os registros e nesse caso ele e sua respectiva cláusula ORDER BY podem ser completamente ignorados uma vez que o melhor é que o SQL Server recupere os dados da forma que achar mais conveniente. Isso pode ser claramente representado no plano de execução.

Como podemos observar é feito um SCAN na tabela tblClientes e logo após  é feita uma operação do tipo Compute Scalar (responsável por fazer a concatenação para gerar o nome completo) e por fim o SELECT para exibir os resultados. O ORDER BY foi literalmente desprezado.

O próximo passo é verificar como o SQL Server 2005 e 2008 se comportam com a ordenação interna na View (Nome Completo) seguida da ordenação externa (Data Cadastro).

SELECT * FROM vClientes ORDER BY DataCad

Se utilizarmos uma ordenação externa à View, perceberemos que o duplo SORT não ocorre. Embora a View tenha em seu corpo o uso de um ORDER BY, esse foi completamente ignorado e apenas o ORDER BY externo foi utilizado.

Isso significa dizer que no SQL Server 2005 e 2008, se uma cláusula ORDER BY for adicionada na View com o artifício de TOP 100 PERCENT essa cláusula é completamente ignorada já que afinal o duplo sort é maléfico (e inútil) para um bom desempenho. Ainda que não haja o duplo SORT, acreditar que o ORDER BY com TOP 100 PERCENT irá retornar os dados ordenados é um equívoco, pois, como vimos no plano anterior, o operador SORT não aparece no plano. Mesmo que a View possua o ORDER BY os registros não serão recuperados na ordem.

Se você possui o SQL Server 2000, não deve colocar o ORDER BY na View com o TOP 100 PERCENT, pois, isso pode levar ao problema do duplo SORT. Se você possui o SQL Server 2005 ou 2008 não deve colocar o ORDER BY na View com o TOP 100 PERCENT simplesmente porque ele não tem nenhum efeito e pode ser desagradável você supor que os registros virão em ordem quando na verdade isso não acontece. A melhor prática é criar Views sem a cláusula ORDER BY com o TOP 100 PERCENT e colocar o ORDER BY de forma externa, ou seja, no comando de SELECT.

Alguns argumentarão que o custo de duplo SORT pode ser desprezado, já que um SELECT idealmente deve retornar poucos registros e ele não faria diferença. Há também outro argumento de que o duplo SORT só ocorre se a View possuir uma ordem e se o SELECT mudar essa ordem. O último argumento irá defender que a utilização de índices irá diminuir os custos de SORT se eles ocorrerem. Embora corretos, são argumentos típicos daqueles que se recusam a rever suas boas práticas de código, mas à medida que o SQL Server 2000 está cada vez mais distante da realidade atual e as instalações contemplam o SQL Server 2005 e 2008, o duplo SORT ou até a recuperação ordenada para um JOIN não são mais os grandes problemas. Se o SQL Server 2005 e 2008 forem utilizados temos a certeza de que o ORDER BY com o TOP 100 PERCENT não fará de fato nenhum efeito. Aqueles que o utilizam pensarão que a View está ordenada quando na verdade não está.

Uma pergunta que pode surgir é o que aconteceria com situações que envolvam ranqueamento. Nesse caso não há problema, pois, a ordem é necessária para garantir os registros corretos e será respeitada. Um exemplo pode ser demonstrado conforme o script abaixo:

CREATE VIEW vTOP10ClientesAntigos
AS
SELECT TOP
10 WITH TIES
    Nome + ‘ ‘ + NomeDoMeio + ‘ ‘ + SobreNome AS NomeCompleto,
    CPF, DataCad, CEP FROM tblClientes
ORDER BY DataCad

Para descobrir quem são os dez clientes mais antigos é necessário verificar quais são os dez clientes que tem a data de cadastro mais antiga. Esse é o código que a View se propõe a executar e são retornados os clientes mais antigos. Em todo caso, o que aconteceria se fosse necessário retornar os 10 clientes mais antigos, mas ordenados por NomeCompleto ? Nesse caso um SELECT sobre a View com a cláusula ORDER BY é a escolha óbvia.

SELECT * FROM vTOP10ClientesAntigos ORDER BY NomeCompleto

O plano de execução dessa consulta é exibido logo abaixo:

Se observarmos o plano de execução perceberemos as seguintes tarefas:

  • O operador Table SCAN varre todos os registros da tabela de clientes
  • O operador SORT organiza os registros por ordem de data de cadastro
  • O operador Compute Scalar monta o nome completo contaneando as partes individuais do nome
  • O operador TOP é aplicado para filtrar apenas os 10 primeiros registros
  • O operador SORT organiza o resultado por ordem de nome

Embora possa parecer que há um custo de ordenação desnecessário já que os resultados finais são exibidos por ordem de nome, o plano de execução acima não está errado. Ele está correto e é exatamente o mesmo independente de ser executado no SQL Server 2000, 2005 ou 2008. Ocorre que o primeiro SORT é necessário para descobrir quem são os 10 clientes mais antigos, ou seja, ele realmente é imprescindível para que esse conjunto de 10 clientes seja montado e não se trata de mera estética para exibir os dados ordenados. Já o segundo SORT é realizado apenas para retornar o conjunto dos 10 clientes de forma ordenada por nome completo.

O ORDER BY em Views é incorreto ou desnecessário quando utilizado com o único objetivo de formatar resultados, mas se houver um ranqueamento envolvido ele é imprescindível. Com o avanço do SQL Server 2005 e 2008, utilizar ORDER BY em Views é claramente uma péssima prática e que não retorna o resultado esperado.

[ ]s,

Gustavo

4 Respostas para “Piores práticas – Utilizar a cláusula ORDER BY em Views

  1. Fabiano Neves

    Maia muito bem abortado, eu fico adimirado como mesmo com MVPs, Gurus, Livros, e etc… Recomendando que os usuários não utilizem ORDER BY em views. Agente acaba sempre topando com uma dessas. Já vi algumas mentes brilhantes que colocam “TOP (99.99) PERCENT” ou então “TOP 214748367”, para fazer com que o SQL 2005/2008 aja igual ao SQL 2000.A MS até lançou um KB para "Corrigir" este comportamento no SQL 2005 e 2008. É mole?http://support.microsoft.com/?scid=kb%3Ben-us%3B926292&x=15&y=11Me desculpa perguntar, mas “fix” o que? :-)Na minha opinião algum cliente MUITO grande de SQL, apertou a MS e eles acabaram lançando este FIX. Outra coisa, em pleno ano de 2009, não entendo porque raios o Wizard do SQL ainda deixa o cara ordenar uma coluna, já que isso não sortirá efeito.

  2. Oi Fabiano,Legal as informações…Essa era uma coisa que eu ficava impressionado quando via os desenvolvedores mexendo no Enterprise Manager… Fiquei feliz que no 2005 isso foi "concertado", mas nunca pensei que alguém conseguisse chegar em construções do tipo 99.99 PERCENT ou ainda um TOP com um valor muito grande (se tivesse tido essa idéia ou visto teria abordado no artigo).Essa do FIX é o fim da picada. Também acho que alguém aí deve ter apertado, mas é insistir no erro. Será que o FIX o comportamento volta a ser do 2000. Putz…Abs,

  3. A dica do TOP (99.99) me salvou! Precisava de um SELECT aqui com mais dois SELECT’s com SELECT MAX(id) e SELECT MIN(id) que eu precisava ordenar na View.

Deixe uma resposta

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s