¿Por qué usar Select Top 100 Percent?

EL PORCENTAJE SUPERIOR (100) no tiene ningún sentido en las versiones recientes de SQL Server, y (junto con el ORDEN correspondiente, en el caso de una definición de vista o tabla derivada) es ignorado por el procesador de consultas.

Tienes razón en que érase una vez, que podría ser utilizado como un truco, pero incluso entonces no era fiable. Lamentablemente, algunas de las herramientas gráficas de Microsoft ponen esta cláusula sin sentido.

En cuanto a por qué esto podría aparecer en SQL dinámico, no tengo idea. Tienes razón. no hay razón para ello, y el resultado es el mismo sin ella (y de nuevo, en el caso de una definición de vista o tabla derivada, sin las cláusulas TOP y ORDER BY).

...permitir el uso de un ORDEN POR en una definición de vista.

Esa no es una buena idea. Una vista nunca debe tener un ORDEN POR definido.

Una ORDEN BY tiene un impacto en el rendimiento - usarla como vista significa que la ORDEN BY aparecerá en el plan de explicación. Si tiene una consulta en la que la vista se une a cualquier elemento de la consulta inmediata, o se hace referencia a ella en una vista en línea (factorización CTE / subconsulta), el ORDEN BY siempre se ejecuta antes del ORDEN final BY (suponiendo que definido). No hay beneficio en ordenar filas que no son el conjunto de resultados finales cuando la consulta no está usando TOP (o LIMIT para MySQL/Postgres).

Considere:

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

...es el 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

Esto es malo porque:

1. El ejemplo es ordenar la lista inicialmente por la descripción del elemento, y luego se reordena en función de la descripción del tipo de elemento. Es recursos desperdiciados en la primera ordenación-corriendo como lo hace no significa que se está ejecutando: ORDER BY item_type_description, item_description
2. No es obvio por qué se ordena la vista debido a la encapsulación. Esto no significa que deba crear varias vistas con diferentes órdenes de ordenación...

Si no hay una cláusula ORDER BY, entonces TOP 100 PERCENT es redundante. (Como usted menciona, este fue el 'truco' con vistas)

[Con suerte el optimizador optimizará esto.]

No hay razón más que indiferencia, supongo.

Estas cadenas de consulta generalmente son generadas por una herramienta de consulta gráfica. El usuario se une a algunas tablas, agrega un filtro, un orden de clasificación y prueba los resultados. Dado que el usuario puede querer guardar la consulta como una vista, la herramienta agrega un 100 POR CIENTO SUPERIOR. En este caso, sin embargo, el usuario copia el SQL en su código, parametriza la cláusula WHERE y oculta todo en una capa de acceso a datos. Fuera de la mente, fuera de la vista.

He visto otro código que he heredado que utiliza SELECT TOP 100 PERCENT

La razón de esto es simple: Enterprise Manager solía tratar de ser útil y formatear su código para incluirlo por usted. No tenía sentido nunca tratar de eliminarlo, ya que realmente no dolía nada y la próxima vez que fue a cambiarlo EM lo insertaría de nuevo.

Amablemente intente lo siguiente, espero que funcione para usted.

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

Supongo que puede usar una variable en el resultado, pero aparte de obtener el ORDEN POR pieza en una vista, no verá un beneficio al declarar implícitamente "TOP 100 PERCENT":

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

Solo prueba esto, se explica más o menos por sí mismo. No puede crear una vista con un PEDIDO POR excepto si...

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

Msg 1033, Nivel 15, Estado 1, Procedimiento TestView, Línea 5 El ORDEN POR la cláusula no es válida en vistas, funciones en línea, tablas derivadas, subconsultas y expresiones de tabla comunes, a menos que TOP, OFFSET o FOR También se especifica XML.