Views and the ORDER BY Clause

• hawg

  SSCertifiable

  Points: 6044

  More actions

  August 1, 2006 at 4:11 pm

  #61068

  I have two tables called employee_table and contractor_table.  They are created using the following script:

   

  CREATE TABLE dbo.employee_table(

              employee_id                 varchar(15),

              first_name                     varchar(50),

              last_name                     varchar(50),

              state                             char(2))

   

  CREATE TABLE dbo.contractor_table(

              contractor_id                varchar(15),

              first_name                     varchar(50),

              last_name                     varchar(50),

              state                             char(2))

   

  The values in the respective tables are:

   

  Employee_table

   

  Employee_id	First_name	last_name	State
  800485	Bob	Stevens	AR
  801842	John	Smith	TX
  800022	Alex	Anderson	AR
  800588	Stephanie	Jacobs	AR

   

   

  Contractor_table

   

  Contractor_id	First_name	last_name	State
  990123	Ian	Craft	TX
  991483	Bubba	Gump	TX
  990089	Zenon	Williams	AR

   

   

  Using this data, we have a view that has the following design:

   

  CREATE VIEW dbo.v_My_View AS

  SELECT TOP 100 PERCENT (‘0’ + SUBSTRING(employee_id, 2, 8)) AS employee_alias, employee_id, first_name, last_name, state

  FROM dbo.employee_table

  ORDER BY last_name, first_name, employee_id

  UNION ALL

  SELECT TOP 100 PERCENT contractor_id AS employee_id, contractor_id, first_name, last_name, state

  FROM dbo.contractor_table

  ORDER BY last_name, first_name, contractor_id

   

  First, one of our DB guys wanted to change it and he opened it in a design window from EM and when he clicked the RUN button he got an error saying “incorrect syntax near the keyword UNION”.  When he clicked the Verify Syntax button it said the syntax was verified against the data source.  We can’t understand how this even worked in the first place if there is an error.  Can anyone explain this?  Why does it verify OK but not run?  Any idea how it ever ran?

   

  Secondly, when we run the query that creates the view we get the data in the order of the first query ordered as it is stated then the records from the second query ordered as the second query is ordered.  The output looks like this:

   

  Employee_alias	Employee_id	First_name	Last_name	State
  000022	800022	Alex	Anderson	AR
  000588	800588	Stephanie	Jacobs	AR
  001842	801842	John	Smith	TX
  000485	800485	Bob	Stevens	AR
  990123	990123	Ian	Craft	TX
  991483	991483	Bubba	Gump	TX
  990089	990089	Zenon	Williams	AR

   

  What we really want is to get the data in the following order (all the records from both tables sorted together):

   

  Employee_alias	Employee_id	First_name	Last_name	State
  000022	800022	Alex	Anderson	AR
  990123	990123	Ian	Craft	TX
  991483	991483	Bubba	Gump	TX
  000588	800588	Stephanie	Jacobs	AR
  001842	801842	John	Smith	TX
  000485	800485	Bob	Stevens	AR
  990089	990089	Zenon	Williams	AR

   

  We have tried various ideas.  Can anyone tell me how to do this? 

   

  Third, whenever we create the view, no matter which query we use, the create works OK but when we query the view there is debate over why we see the data we do.

   

  When we query the view without an ORDER BY clause, the data is not necessarily ordered in the way the query that created the view is ordered.  I say this is by design in that when querying a table (or a view), unless an ORDER BY clause is used, the sort order cannot be guaranteed.  Is this the case or should a view default to the order of the CREATE VIEW query?

   

  Thanks for your help.

   

  hawg

  ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

• Lance Sanders

  SSC Eights!

  Points: 903

  More actions

  August 1, 2006 at 6:35 pm

  #652711

  Let's take 'em one at a time.

  First the SQLEM error. A UNION construct can only have one ORDER BY clause. If you try to run the statement from the view directly, you'll get the same error. Why it compiles is beyond me, but if you generate an execution plan on a select from the view, you'll see that it's not sorting at all.

  Second, to force the ORDER BY to work, use a derived table:

    alter VIEW dbo.v_My_View AS
    select top 100 percent * from (
      SELECT ('0' + SUBSTRING(employee_id, 2, 8)) AS employee_alias, employee_id, first_name, last_name, state
      FROM dbo.employee_table
      UNION ALL
      SELECT contractor_id AS employee_id, contractor_id, first_name, last_name, state
      FROM dbo.contractor_table
    ) as x
    ORDER BY 4,3,2
    go

  Third(ly), see #1. Since the plan was generated omitting the ORDER BY, the results you are seeing without specifying a sort order are the likely result of the storage order of the data. You don't happen to have a clustered index on last_name,first_name, do you?

  I'm sure that through your trial and error, you've found that SQL really doesn't like having an ORDER BY in a view, forcing you to use the TOP keyword to circumvent this restriction. If possible, resist the urge to order within the view, and instead order in your select statement. Sorts are a fairly costly operation. Specifying the ORDER BY both within the view and in the select from the view results in the sort operation being performed twice, which ultimately hurts performance.