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. 😎
I think it does. Give this a shot.
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
Then run these selects and note the difference.
Select * From dbo.vw_GetAllEmpoyeesByHireDate;
Select * From dbo.vw_GetAllEmpoyeesByHireDate Order By Title;
Select * From dbo.vw_GetAllEmpoyeesByHireDate Order By HireDate;
May need more testing though.
Enjoy!