Why use Select Top 100 Percent

Question

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

User · Answer

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    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 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

User · Answer

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   gt  100   And this may also fail because  SELECT foo FROM      SELECT foo From MyTable WHERE ISNUMERIC  foo    1  bar WHERE     CAST foo AS int   gt  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   gt  100   Note  this still works in SQL Server 2005  SELECT TOP 2000000000     ORDER BY

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  100

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Why use Select Top 100 Percent?

Examples related to sql

Examples related to sql-server

Examples related to tsql