Por que usar o Select Top 100 Percent?

Eu entendo que antes do SQL Server 2005 , você poderia “enganar” o SQL Server para permitir o uso de uma ordem em uma definição de visualização, incluindo também TOP 100 PERCENT na cláusula SELECT . Mas eu vi outro código que eu herdei que usa SELECT TOP 100 PERCENT … dentro de instruções SQL dinâmicas (usadas no ADO em aplicativos ASP.NET , etc). Existe alguma razão para isso? O resultado não é o mesmo que não include os TOP 100 PERCENT ?

Foi usado para ” materialização intermediária (pesquisa do Google) ”

Bom artigo: Adam Machanic: Explorando os segredos da materialização intermediária

Ele até criou um MS Connect para que possa ser feito de maneira mais limpa

Minha opinião é “não inerentemente ruim”, mas não use a menos que 100% de certeza. O problema é que ele funciona apenas no momento em que você o faz e provavelmente não mais tarde (nível de patch, esquema, índice, contagens de linhas, etc) …

Exemplo trabalhado

Isso pode falhar porque você não sabe em que ordem as coisas são avaliadas

 SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100 

E isso também pode falhar porque

 SELECT foo FROM (SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar WHERE CAST(foo AS int) > 100 

No entanto, isso não no SQL Server 2000. A consulta interna é avaliada e colocada em spool:

 SELECT foo FROM (SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar WHERE CAST(foo AS int) > 100 

Observe, isso ainda funciona no SQL Server 2005

 SELECT TOP 2000000000 ... ORDER BY... 

TOP (100) PERCENT é completamente insignificante nas versões recentes do SQL Server e (juntamente com o ORDER BY correspondente, no caso de uma definição de exibição ou tabela derivada) é ignorado pelo processador de consultas.

Você está certo de que, uma vez, poderia ser usado como um truque, mas mesmo assim não era confiável. Infelizmente, algumas das ferramentas gráficas da Microsoft colocam essa cláusula sem sentido.

Quanto a por que isso pode aparecer no SQL dynamic, não tenho idéia. Você está certo de que não há motivo para isso e o resultado é o mesmo sem ele (e, novamente, no caso de uma definição de exibição ou tabela derivada, sem as cláusulas TOP e ORDER BY).

… permite o uso de um ORDER BY em uma definição de visualização.

Isso não é uma boa idéia. Uma visão nunca deve ter um ORDER BY definido.

Um ORDER BY tem um impacto no desempenho – usando uma visão significa que o ORDER BY aparecerá no plano de explicação. Se você tiver uma consulta em que a visualização é unida a qualquer coisa na consulta imediata ou referenciada em uma visualização sequencial (CTE / subquery factoring) – o ORDER BY é sempre executado antes do ORDER BY final (supondo que ele tenha sido definido). Não há nenhum benefício em ordenar as linhas que não são o resultado final quando a consulta não está usando TOP (ou LIMIT for MySQL / Postgres).

Considerar:

 CREATE VIEW my_view AS SELECT i.item_id, i.item_description, it.item_type_description FROM ITEMS i JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id ORDER BY i.item_description 

  SELECT t.item_id, t.item_description, t.item_type_description FROM my_view t ORDER BY t.item_type_description 

… é o equivalente a usar:

  SELECT t.item_id, t.item_description, t.item_type_description FROM (SELECT i.item_id, i.item_description, it.item_type_description FROM ITEMS i JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id ORDER BY i.item_description) t ORDER BY t.item_type_description 

Isso é ruim porque:

  1. O exemplo é ordenar a lista inicialmente pela descrição do item e, em seguida, ela é reordenada com base na descrição do tipo de item. É um desperdício de resources na primeira sorting – executar como está não significa que esteja em execução: ORDER BY item_type_description, item_description
  2. Não é óbvio a que a vista é ordenada devido ao encapsulamento. Isso não significa que você deve criar vários modos de exibição com ordens de sorting diferentes …

Se não houver uma ORDER BY , TOP 100 PERCENT será redundante. (Como você mencionou, este foi o ‘truque’ com vistas)

[Espero que o otimizador otimize isso.]

Nenhuma razão, mas indiferença, eu acho.

Essas strings de consulta são geralmente geradas por uma ferramenta de consulta gráfica. O usuário ingressa em algumas tabelas, adiciona um filtro, uma ordem de sorting e testa os resultados. Como o usuário pode querer salvar a consulta como uma visualização, a ferramenta adiciona um percentual SUPERIOR a 100%. Nesse caso, porém, o usuário copia o SQL em seu código, parametriza a cláusula WHERE e oculta tudo em uma camada de access a dados. Fora da mente, fora da vista.

Eu vi outro código que eu herdei e que usa SELECT TOP 100 PERCENT

A razão para isso é simples: o Enterprise Manager costumava tentar ser útil e formatar seu código para include isso para você. Não havia sentido em tentar removê-lo, já que não doía nada e, da próxima vez que você mudasse, o EM o inseriria novamente.

Por favor, tente o abaixo, espero que funcione para você.

  SELECT TOP ( SELECT COUNT(foo) From MyTable WHERE ISNUMERIC (foo) = 1) * FROM bar WITH(NOLOCK) ORDER BY foo WHERE CAST(foo AS int) > 100 ) 

Eu suponho que você pode usar uma variável no resultado, mas além de obter a peça ORDER BY em uma exibição, você não verá um benefício declarando implicitamente “TOP 100 PERCENT”:

 declare @t int set @t=100 select top (@t) percent * from tableOf 

Basta tentar isso, explica-se praticamente em si. Você não pode criar uma visão com um ORDER BY, exceto se …

 CREATE VIEW v_Test AS SELECT name FROM sysobjects ORDER BY name GO 

Msg 1033, nível 15, estado 1, procedimento TestView, linha 5 A cláusula ORDER BY é inválida em modos de exibição, funções embutidas, tabelas derivadas, subconsultas e expressões de tabela comuns, a menos que TOP, OFFSET ou FOR XML também é especificado.