Desafio: Usando ORDER BY dentro de uma VIEW


Deixo compartilhar uma situação que ocorreu no trabalho: o desenvolvedor utilizava uma view para retornar os dados ordenados. Segundo ele, o comando abaixo funcionava no SQL 2000, mas deixou de funcionar no SQL 2005.



CREATE VIEW vwOperacao
AS
SELECT
* FROM tbOperacao
ORDER BY data


Msg 1033, Level 15, State 1, Procedure vwOperacao, Line 4
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.

Seguindo as orientações da própria mensagem, ele decidiu utilizar a seguinte notação:


CREATE VIEW vwOperacao
AS
SELECT TOP
100 PERCENT * FROM tbOperacao
ORDER BY data

Qual o problema nisso? Ao consultar a View, os resultados não obedecem ao ORDER BY.


select * FROM vwOperacao WHERE oper = ‘JOE’

Perguntas:


1) Por que o comando ORDER BY é inválido em VIEWS (Erro 1033) exceto se houver a expressão TOP?


2) Por que os resultados retornam fora de ordem apesar do ORDER BY estar definido na View?


3) Qual seria uma correção rápida? (sim, existe um quebra-galho!)


Quem souber a resposta, por favor, poste nos comentários! (Utilize o script anexado no post para criar a tabela)

DesafioOrderBy.sql

Comments (6)

  1. Marcelo Fernandes disse:

    Bom consegui responder a questão 3 que é a mais fácil… as questões 1 e 2 foi fazer mais alguns testes para saber se estou no caminho..

    Bom consegui contornar de 3 modos

    1- usando o provérbio 99 não é 100, mas 99.999 é praticamente 100 😀

    CREATE VIEW vwOperacao

    AS

    SELECT TOP 99.999 PERCENT * FROM tbOperacao

    ORDER BY data

    2- podemos complicar mais um pouco  e criar um PK Clustered no campo id, o que causaria um clustered index scan, mas o dados retornarian organizados pelo id, sendo assim a solução nao gosto muito mas apelie… criei um indice na coluda data e forcei o hint na view

    alter table tbOperacao alter column id int not null

    go

    –cria pk clustered

    alter table tbOperacao add constraint pk_tbOperacao primary key clustered (id)

    –cria indice

    go

    create index ix_tbOperacao_01 on tbOperacao (data)

    CREATE VIEW vwOperacao

    AS

    SELECT * FROM tbOperacao with(index=ix_tbOperacao_01)

    3- usando as as novas funcionalidades presentes no 2005 e 2008, usei uma cte u usei o order :p

    CREATE VIEW vwOperacao

    AS

    with cte_dados as (

    SELECT ROW_NUMBER() over(order by data) as num,* FROM tbOperacao

    )

    select * from cte_dados

    Abs

    Marcelo Fernandes

  2. Marcelo Fernandes disse:

    Respondendo a questão 2, uma view é um statement com a query, ou seja é uma tabela virtual, logo esta tabela virtual é montada no momento em que se invoca o objeto, e o dados são inseridos conforme está na tabela original, satisfazendo a query, o SQL não insere os dados organizados nesta "tabela virtual", assim como os inserts que fazemos no dia a dia, acredito que ninguém insere dados organizado na tabela :D… organiza-se na consulta!

  3. DBA disse:

    Não sei se seria o mais correto, mas o retorno é o esperado. e sem muito codigo:

    create function [dbo].[fnoperacao]()

    returns @retfindreports table

    (

    id int,

    data datetime,

    oper varchar(20)

    )

    as

    begin

     insert @retfindreports

     select * from tboperacao order by data

     return

    end;

    go

    select * from [dsup999].[dbo].[fnoperacao] ()

  4. Excelentes respostas! Responderei às perguntas 1 e 2 propostas. Uma VIEW permite retornar um conjunto de dados a partir de tabelas e relacionamentos. Pela definição matemática, um conjunto não preserva a ordem. Essa é a explicação para as perguntas 1 e 2.

    1)  É incorreto especificar ORDER BY em uma View. A única exceção é quando se utiliza o TOP, que funciona como um filtro dos registros a serem retornados. Por exemplo:

    CREATE VIEW vwUltimosSeraoOsPrimeiros
    AS
    SELECT TOP 5 * FROM tbClassificacao ORDER BY rank

    2) Uma VIEW não garante ordem nos resultados. Utilizar TOP 100 + ORDER BY não faz sentido e o SQL poderia (por que não?) retornar um Warning avisando que o resultado pode não ser o esperado. Na verdade, ele não faz nada e apenas ignora o ORDER BY.

    Vamos às respostas, todas corretas:

    a) Uma das formas de “forçar” a ordem é através de FUNCTIONS, que foi a solução proposta pelo usuário “DBA”. Isso funciona muito bem, exceto pelo fato de que sempre será executado um Table Scan.

    b) A segunda forma é utilizar um TOP 99.9999 PERCENT, que forçaria retornar praticamente toda a tabela. Poderia ser criado um índice para evitar o Table Scan. O problema aqui seria caso a tabela tivesse muitos registros… e consequentemente 0,000001% dos registros poderiam ficar de fora.

    c) A terceira forma é através de Hints. Eu tinha pensado nessa como a melhor solução (mas o Marcelo propôs uma melhor). A desvantagem aqui é que a Hint pode induzir a ordem, mas não garante 100%. Existem casos que os registros podem retornar na ordem incorreta. Exemplo: após obter os resultados do índice, o otimizador decide aplicar um operador de HASH.

    d) A quarta forma é utilizando o ROW_NUMBER(), que forçaria o Optimizer a criar uma ordenação nos dados. Achei essa solução muito elegante.

    e) Alguma outra sugestão? Postem comentários.

    Todas essas soluções são excelentes QUEBRA-GALHO. Pela definição, uma View não retorna dados ordenados – se o desenvolvedor quiser ordenar dados, então deverá ser realizado explicitamente no comando SELECT * FROM View ORDER BY column.

    Obrigado aos colaboradores DBA e Marcelo. Quem tiver novas sugestões ou idéias criativas, não deixem de postá-las.

    1. Elvis Silva Noleto disse:

      Boa tarde!

      Estava buscando a mesma solução essa manhã, vi o post de vocês e fiquei realmente confiante de que não havia solução, porém mesmo assim continuei buscando, um dos meus amigos desenvolvedores supostamente tinha uma solução, porém essa suposta solução deixou de ser suposta e passou a ser realmente a solução, mediante disso eu digo “É POSSÍVEL CRIAR UMA VIEW COM ORDER BY” e isso sem utilizar a clausula Order by (não no final), ao invés disso pode-se criar um ROW_NUMBER, ao setar o Order by dele toda a tabela passa a seguir essa nova ordem, mesmo dentro da view essa ordem é respeitada, para não apresentar essa coluna adicional criada pode-se utilizar um WITH.

  5. Célio Xavier disse:

    Ótima dica, estava estudando alguns exercícios de relatório de Banco de Dados, de repente, uma surpresa!!!!!! A Virtual Tables não aceita a função ORDER BY, porém com esta dica ficou mais claro de como é declarado neste tipo de relatório! Muito Bom.

Skip to main content