Why use Select Top 100 Percent?

Question

I understand that prior to SQL Server 2005, you could have tricked SQL Server into allowing the use of order in the definition of a definition by also including the TOP 100 PERCENT in the SELECT clause. But I saw another code that I inherited that uses SELECT TOP 100 PERCENT ... in dynamic SQL operations (used in ADO in ASP.NET applications, etc.). Is there a reason for this? Is the result the same as not , including the TOP 100 PERCENT ?

Answer 1

It was used for intermediate materialization (Google search) "

Good article: Adam Machanic: Learning the Secrets of Intermediate Materialization

He even picked up MS Connect so that it could be done in a cleaner way.

My opinion is "not inherently bad," but do not use it if you are not 100% sure. The problem is that it only works when you do this, and probably not later (patch level, schema, index, number of lines, etc.) ...

Work example

This may be unsuccessful because you do not know in what order things are ranked

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

And it can also fail because

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

However, this was not the case in SQL Server 2000. The internal query is evaluated and buffered:

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

Note. It still works in SQL Server 2005

 SELECT TOP 2000000000 ... ORDER BY... 

Answer 2

TOP (100) PERCENT is completely pointless in recent versions of SQL Server, and it (together with the corresponding ORDER BY, in the case of a view or view definition) is ignored by the query processor.

You are right that once this could be used as a trick, but even then it was not reliable. Unfortunately, some of Microsoft's graphics tools put this pointless sentence on.

As for why this might appear in dynamic SQL, I have no idea. You are right that there is no reason for this, and the result will be the same without it (and again, in the case of defining a view or view, without TOP and ORDER BY clauses).

Answer 3

... allow the use of ORDER BY in a view definition.

This is not a good idea. The view should never have an ORDER BY set.

ORDER BY affects performance - using this view means that ORDER BY appears in terms of explanation. If you have a query in which the view is connected to something in the direct query or referenced in the inline view (CTE factoring / subquery), ORDER BY is always run before the final ORDER BY (provided that it is defined). There is no use for arranging rows that are not the final result when the query does not use TOP (or LIMIT for MySQL / Postgres).

Consider:

 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 

... equivalent to using:

  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 

This is bad because:

• The example arranges the list initially by the description of the element, and then it is reordered based on the description of the element type. He spent resources in the first sort - working as is does not mean that he is working: ORDER BY item_type_description, item_description
• It is not obvious that the presentation is ordered due to encapsulation. This does not mean that you should create multiple views with different sort orders ...

Answer 4

If there is no ORDER BY , then TOP 100 PERCENT is redundant. (As you mentioned, it was a "trick" with submissions)

[Hope the optimizer optimizes this.]

Answer 5

No reason other than indifference, I would suggest.

Such query strings are usually generated by the graphical query tool. The user joins several tables, adds a filter, sort order and checks the results. Since the user may want to save the query as a view, the tool adds TOP 100 PERCENT. In this case, the user copies SQL into his code, parameterizes the WHERE clause, and hides everything at the data access level. Out of sight, out of sight.

Answer 6

I saw another code that I inherited that uses SELECT TOP 100 PERCENT

The reason for this is simple: Enterprise Manager used to try to help and format your code to enable this for you. There was no point in trying to remove it, because it didn’t hurt anything, and the next time you went to change it, EM inserted it again.

Answer 7

Please try below, hope it will work for you.

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

Answer 8

I would suggest that you can use the variable as a result, but besides getting the ORDER BY part in the view, you will not see the benefit by implicitly declaring "TOP 100 PERCENT":

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

Answer 9

Just try it, it explains it to a large extent. You cannot create a view using ORDER BY, unless ...

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

Msg 1033, Level 15, State 1, TestView Procedure, Line 5 The ORDER BY clause is not valid in views, built-in functions, views, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified.