92

I understand that prior to SQL Server 2005, you could "trick" SQL Server to allow use of an order by in a view definition, by also include TOP 100 PERCENT in the SELECT clause. But I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT ... within dynamic SQL statements (used in ADO in ASP.NET apps, etc). Is there any reason for this? Isn't the result the same as not including the TOP 100 PERCENT?

Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
Ed Schembor
  • 8,090
  • 8
  • 31
  • 37
  • 5
    maybe there's some statement construction going on: "SELECT TOP {0} PERCENT..." – Michael Petrotta Oct 26 '09 at 02:52
  • 1
    Your first sentence has become the answer one of my hidden question. – Muhammad Ashikuzzaman Feb 10 '19 at 03:13
  • I use the top 99.9999999 PERCENT and it always work. It is close enough. I tend to append number of '9''s based on the expected number of records. More records, more 9's and it always work. I have used across SQL 2008 to SQL 2017. – pedi Feb 27 '19 at 20:51

10 Answers10

57

It was used for "intermediate materialization (Google search)"

Good article: Adam Machanic: Exploring the secrets of intermediate materialization

He even raised an MS Connect so it can be done in a cleaner fashion

My view is "not inherently bad", but don't use it unless 100% sure. The problem is, it works only at the time you do it and probably not later (patch level, schema, index, row counts etc)...

Worked example

This may fail because you don't know in which order things are evaluated

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

And this may also fail because

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

However, this did not in SQL Server 2000. The inner query is evaluated and spooled:

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, this still works in SQL Server 2005

SELECT TOP 2000000000 ... ORDER BY...
Diado
  • 2,229
  • 3
  • 18
  • 21
gbn
  • 422,506
  • 82
  • 585
  • 676
48

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

You're correct that once upon a time, it could be used as a trick, but even then it wasn't reliable. Sadly, some of Microsoft's graphical tools put this meaningless clause in.

As for why this might appear in dynamic SQL, I have no idea. You're correct that there's no reason for it, and the result is the same without it (and again, in the case of a view definition or derived table, without both the TOP and ORDER BY clauses).

Steve Kass
  • 7,144
  • 20
  • 26
  • This isn't true; see this [link](http://sqlblog.com/blogs/adam_machanic/archive/2006/10/03/exploring-the-secrets-of-intermediate-materialization.aspx) from the answer by @gbn for details. – Kenny Evitt Jul 18 '13 at 16:21
  • 6
    The link you reference doesn't say anything about SELECT TOP (100) PERCENT .. ORDER BY, which is meaningless. The link mentions the use of SELECT TOP (2147483647) .. ORDER BY. Currently, the SQL Server optimizer will eliminate SELECT TOP (100) PERCENT .. ORDER BY, because it is meaningless. That combination always defines the same collection of rows as SELECT without TOP/ORDER BY. The optimizer currently does not attempt to determine whether 2147483647 comprises all the rows, so it doesn't eliminate the TOP - ORDER BY combination in this case. – Steve Kass Aug 02 '13 at 05:48
  • 2
    The link actually does mention `TOP (100) PERCENT`: "... I might try forcing intermediate materialization of the derived table, sans the temp table, by using TOP 100 PERCENT in conjunction with ORDER BY. Unfortunately, the SQL Server query optimizer team decided that this wasn't a good idea, and the optimizer now ignores such attempts." It backs you up in fact. – Kenny Evitt Aug 02 '13 at 15:51
  • [I'd undo my downvote if I could](http://meta.stackexchange.com/questions/19940/undo-a-up-down-vote-after-a-comment-is-left/21839#21839). [If you edit your answer I'll upvote it.] – Kenny Evitt Aug 02 '13 at 15:55
  • Right, the link mentions TOP (100) PERCENT, but only to say that it does nothing. I guess I'm not sure what you meant in your first comment by "This isn't true." – Steve Kass Aug 02 '13 at 16:37
  • 2
    You're probably confused because I changed my mind; my first comment was wrong; you're right. – Kenny Evitt Aug 02 '13 at 17:04
25

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

That's not a good idea. A view should never have an ORDER BY defined.

An ORDER BY has an impact on performance - using it a view means that the ORDER BY will turn up in the explain plan. If you have a query where the view is joined to anything in the immediate query, or referenced in an inline view (CTE/subquery factoring) - the ORDER BY is always run prior to the final ORDER BY (assuming it was defined). There's no benefit to ordering rows that aren't the final result set when the query isn't using 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

...is the 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:

  1. The example is ordering the list initially by the item description, and then it's reordered based on the item type description. It's wasted resources in the first sort - running as is does not mean it's running: ORDER BY item_type_description, item_description
  2. It's not obvious what the view is ordered by due to encapsulation. This does not mean you should create multiple views with different sort orders...
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
6

If there is no ORDER BY clause, then TOP 100 PERCENT is redundant. (As you mention, this was the 'trick' with views)

[Hopefully the optimizer will optimize this away.]

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
5

No reason but indifference, I'd guess.

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

Peter Radocchia
  • 10,710
  • 2
  • 34
  • 56
5

I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT

The reason for this is simple: Enterprise Manager used to try to be helpful and format your code to include this for you. There was no point ever trying to remove it as it didn't really hurt anything and the next time you went to change it EM would insert it again.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1

Kindly try the 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
               )
Prabhat Sinha
  • 1,500
  • 20
  • 32
Lakshminarayanan E
  • 29
  • 1
  • 1
  • 6
1

The error says it all...

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

Don't use TOP 100 PERCENT, use TOP n, where N is a number

The TOP 100 PERCENT (for reasons I don't know) is ignored by SQL Server VIEW (post 2012 versions), but I think MS kept it for syntax reasons. TOP n is better and will work inside a view and sort it the way you want when a view is used initially, but be careful.

Fandango68
  • 4,461
  • 4
  • 39
  • 74
0

I would suppose that you can use a variable in the result, but aside from getting the ORDER BY piece in a view, you will not see a benefit by implicitly stating "TOP 100 PERCENT":

declare @t int
set @t=100
select top (@t) percent * from tableOf
Lauren Glenn
  • 11
  • 1
  • 2
    The question is `Why use Select Top 100 Percent` not to get a variable count for the Percent. – bummi Nov 18 '14 at 18:00
0

Just try this, it explains it pretty much itself. You can't create a view with an ORDER BY except if...

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

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

Prabhat Sinha
  • 1,500
  • 20
  • 32
John Brewster
  • 1
  • 1
  • 4