SanDroid (10/21/2011)

---

...I should have been more specific in my question.
Does using a "TOP 99999999" statement with an "order by" in a VIEW against an actual table with less than 99.9 Million rows keep the query optimizer from ignoring the Order by statement since nothing will be filtered?

I know in a basic select with a variable it will not, but Views are different.

USE [AdventureWorks];
GO

/****** Object:  View [dbo].[vw_GetAllEmpoyeesByHireDate]    Script Date: 10/21/2011 13:56:54 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_GetAllEmpoyeesByHireDate]'))
   DROP VIEW [dbo].[vw_GetAllEmpoyeesByHireDate];
GO

/****** Object:  View [dbo].[vw_GetAllEmpoyeesByHireDate]    Script Date: 10/21/2011 13:56:55 ******/
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE VIEW [dbo].[vw_GetAllEmpoyeesByHireDate]
AS

SELECT TOP ((SELECT COUNT(*) FROM HumanResources.Employee) + 100)
       LoginID
     , Title
     , HireDate
  FROM
       HumanResources.Employee AS emp
 ORDER BY HireDate DESC;
GO

Select * From dbo.vw_GetAllEmpoyeesByHireDate;

Select * From dbo.vw_GetAllEmpoyeesByHireDate Order By Title;

Select * From dbo.vw_GetAllEmpoyeesByHireDate Order By HireDate;