Differences between SQL2005 views and SQL2000 views

• Bert Otte

  Newbie

  Points: 1

  More actions

  January 13, 2006 at 3:25 am

  #188851

  Hello,

  After upgrading from SQL2000 to SQL2005 I got the following problem.

  I'm using the following view called say view1

  SELECT     TOP (100) PERCENT ot.taal, ok.child AS organisatie, ot.sleutel, ok.parentlft

  FROM         dbo.organisatiekinderenview AS ok INNER JOIN

                        dbo.organisatietaal AS ot ON ot.organisatie = ok.parent

  WHERE     (ot.standaard = 1)

  ORDER BY ok.parentlft DESC

  When I'm executing the next query

  select * from view1 the following difference occurs.

  In SQL2000 the list is ordered by parentlft and in SQL2005 the list is unordered.

  Does anyone know what the problem is?

  Bert Otte

   

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  January 16, 2006 at 2:11 pm

  #614997

  >>In SQL2000 the list is ordered by parentlft and in SQL2005 the list is unordered. 

  It was/is a hack in Sql2000 and worked by accident.

  A view is just a virtual table, and like a table, is just a set of unordered rows.

  ORDER BY can be used in a view in conjunction with TOP - use of TOP 100 PERCENT was a hack to get all rows, and get them sorted.