View sort order problems

View sort order problems

• jyothsna

  The only way to include ORDER BY in a view in SQL Server 2000 is to use TOP 100 PERCENT. This is a kludge at best, and I think it was a coincidence that it seemed to work. In my opinion, they FIXED this problem in SQL Server 2005. Have you tried setting compatibility level to 80 to see if the behavior changes Anyway, you should never rely on this (just like you should never rely on a clustered index to dictate the order of SELECT * without ORDER BY). The view itself is not really ordered by anything. Like a table, a view is just an unordered collection of rows, and without a specific ORDER BY on the outermost query, the optimizer is free to return the rows in the most efficient way it can, which is not necessarily going to obey any internal ordering (whether you put it on the view, or whether you expect the clustered index or physical/chronological ordering to be obeyed). To get the view to show you results in a certain order, you say: SELECT col_list FROM view_name ORDER BY ... Period. wrote in message news:fa0b7667-cd33-416b-a41c-36807f13fe62@discussions.microsoft.com... >I am testing an upgrade of our databases to SQL Server 2005 (from 2000), > and I apologize if this question seems a little simplistic. I have a > view that is based on a table where the primary key is Doctor #, and it > even has an ORDER BY to sort by Doctor #. This view has ALWAYS (in SQL > 2000) returned the data in the proper sort order, but once I upgraded a > copy of the database to SQL Server 2005 the view resultset is no longer > in Doctor # order! When I connect to the production SQL Server 2000 > database from the 2005 Management Studio and query the table, it returns > the properly sorted results! To further confuse things, when I right > click on a view (in Management Studio) and select 'Open View', I get the > unsorted results, but if I select 'Modify' from the context menu and > then, while in edit mode, execute the query for the view - it IS sorted > properly! This behavior occurs on various different views. > > I am probably missing something simple here, but I just can't seem to > get a handle on what it is. >
  

• ButlerDJ

  >I understand what you are saying 'NNTP User', but this "Kludge" was a
  > tip that came from SQL Server Magazine some time back (I don't remember
  > when, exactly, but I have seen this tip elsewhere, too) for SQL Server
  > 2000 - so I guess you are questioning their suggestions 
  
  Yep. And so has Microsoft, apparently, or everything would be hunky-dorey, 
  wouldn't it 
  
  > Anyway, your
  > points are well-taken, but appear to miss the point of the problems that
  > were encountered.
  
  Um, okay. The problem is that when you say "SELECT * FROM view" it doesn't 
  give you the results in the right order. Modify/Execute/Open, whatever, if 
  you want to have the view return results in a specific order, use order by 
  on the outer query, not the view itself. 
  
  
  

  
  

• Anonymous2342323432

  That is fine, and thank you for your efforts.  I appreciate your information.
  

• Queen of the desert

  I understand what you are saying 'NNTP User', but this "Kludge" was a tip that came from SQL Server Magazine some time back (I don't remember when, exactly, but I have seen this tip elsewhere, too) for SQL Server 2000 - so I guess you are questioning their suggestions   Anyway, your points are well-taken, but appear to miss the point of the problems that were encountered.
  

• music_at_ulan

  > "To further confuse things, when I right click on a view (in Management
  > Studio) and select 'Open View', I get the unsorted results, but if I
  > select 'Modify' from the context menu and then, while in edit mode,
  > execute the query for the view - it IS sorted properly!"
  >
  > I appreciate you trying to help, though, obviously with some irritation
  > it seems, but I think you are still missing the point of the question.
  > Thanks anyway.
  
  My point is that the order is not guaranteed. So you should not expect any 
  two different behaviors within Management Studio to return the exact same 
  result, in fact you might even see the same behavior return the result in a 
  different order between executions.
  
  I am not missing the point of the question. I am just frustrated with 
  people who assume that adding a kludge order by to a view means that the 
  view is "ordered" and then expect every single interaction with that view to 
  return the results in some fantasy order. A VIEW, LIKE A TABLE, IS 
  UNORDERED BY DEFINITION.
  
  If you really want to know what is going on in Management Studio when you're 
  doing all these weird things with views, then watch profiler in both cases. 
  My *guess* is that one or the other is either enclosed in an outer query 
  with an ORDER BY (which will make the outer query SEEM to obey the order by 
  within the view) or appended with a MAXDOP hint (which will prevent 
  parallelism from affecting the resultset). I am a little too annoyed with 
  being accused of missing the point, to do any more work for you. 
  
  
  

  
  

• Mr. Bungle

  >I understand what you are saying 'NNTP User', but this "Kludge" was a
  > tip that came from SQL Server Magazine some time back (I don't remember
  > when, exactly, but I have seen this tip elsewhere, too) for SQL Server
  > 2000 - so I guess you are questioning their suggestions 
  
  Yep. And so has Microsoft, apparently, or everything would be hunky-dorey, 
  wouldn't it 
  
  > Anyway, your
  > points are well-taken, but appear to miss the point of the problems that
  > were encountered.
  
  Um, okay. The problem is that when you say "SELECT * FROM view" it doesn't 
  give you the results in the right order. Modify/Execute/Open, whatever, if 
  you want to have the view return results in a specific order, use order by 
  on the outer query, not the view itself. 
  
  
  

  
  

• Lucianamaría

  > "To further confuse things, when I right click on a view (in Management
  > Studio) and select 'Open View', I get the unsorted results, but if I
  > select 'Modify' from the context menu and then, while in edit mode,
  > execute the query for the view - it IS sorted properly!"
  
  My new guess here is that Management Studio says "SELECT * FROM View" when 
  you use 'open view' but executes the actual query code (including the ORDER 
  BY) without referencing the view itself, when you use Modify/Execute. My 
  suggestion, for consistency, is to use Script to new query window as alter 
  if you're going to work on the view. 
  
  
  

  
  

• tcheck

  I apologize if I'm not explaining myself very well, and I am simply asking a question as to particular behaviors that vary within the Management Studio, for example:
  
  "To further confuse things, when I right click on a view (in Management Studio) and select 'Open View', I get the unsorted results, but if I select 'Modify' from the context menu and then, while in edit mode, execute the query for the view - it IS sorted properly!"
  
  I appreciate you trying to help, though, obviously with some irritation it seems, but I think you are still missing the point of the question.  Thanks anyway.
  
• View sort order problems