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

• Anonymous
  April 17, 2010
  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 :D 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

• Anonymous
  April 17, 2010
  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!

• Anonymous
  April 20, 2010
  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] ()

• Anonymous
  April 20, 2010
  The comment has been removed

  • Anonymous
    November 23, 2016
    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.

• Anonymous
  September 16, 2015
  Ó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.