/*============================================================================
  File:     Top 100 Percent in SQL Server 2005.sql

  Summary:  In SQL Server 2005, TOP 100 Percent cannot be used to 
			to ORDER the data within a view. (1) Is there another way?
			and (2) do you really want to use it? Be sure to review
			the entire script.
  
  Date:     June 2006

  SQL Server Version: 9.00.2047.00 (SP1)
------------------------------------------------------------------------------
  Copyright (C) 2006 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE northwind
go

-- These samples use the northwind database. You can backup and 
-- restore the northwind database from SQL Server 2000. 

CREATE VIEW OrderSubTotalsViewNOTOrdered 
AS
SELECT OD.OrderID,
	 SUM(CONVERT(money,(OD.UnitPrice
		*Quantity*(1-Discount)/100))*100) AS SubTotal
FROM [Order Details] AS OD
GROUP BY OD.OrderID
GO

CREATE VIEW OrderSubTotalsViewOrdered 
AS
SELECT TOP 100 PERCENT OD.OrderID,
	 SUM(CONVERT(money,(OD.UnitPrice
		*Quantity*(1-Discount)/100))*100) AS SubTotal
FROM [Order Details] AS OD
GROUP BY OD.OrderID
ORDER BY SubTotal DESC
go

SELECT * FROM dbo.OrderSubTotalsViewNOTOrdered 
ORDER BY SubTotal DESC
go

SELECT * FROM dbo.OrderSubTotalsViewOrdered
go

-- WAIT - this doesn't work in SQL Server 2005!!!
-- In SQL Server 2000, the TOP 100 PERCENT with ORDER BY
-- WORKED (as a trick) BUT, really - you shouldn't be able
-- to order data in a view so in SQL Server 2005 they have 
-- removed this "feature". So, do you have an option if
-- you REALLY want to have the data in a view ordered?

-- Yes - you can use a percentage that's NOT exactly possible
-- and SQL Server will round "up" to essentially 100 percent...
-- OR you can use a number that's WAY above the number of actual
-- rows in the table.

-- (this was a very clever idea from Meinrad Weiss - a fellow
--  RD in Switzerland... he came up with this after I said that 
--  the trick no longer worked... BUT, I'm still VERY hesitant to 
--  recommend this without making sure that you understand the 
--  implications... make sure to keep reading and test this entire 
--  script.)

CREATE VIEW OrderSubTotalsViewOrdered2 
AS
SELECT TOP 99.999 PERCENT OD.OrderID,
	 SUM(CONVERT(money,(OD.UnitPrice
		*Quantity*(1-Discount)/100))*100) AS SubTotal
FROM [Order Details] AS OD
GROUP BY OD.OrderID
ORDER BY SubTotal DESC
go

-- OR

-- (this next view using the MAX bigint value)
CREATE VIEW OrderSubTotalsViewOrdered3 
AS
SELECT TOP 9223372036854775807 OD.OrderID,
	 SUM(CONVERT(money,(OD.UnitPrice
		*Quantity*(1-Discount)/100))*100) AS SubTotal
FROM [Order Details] AS OD
GROUP BY OD.OrderID
ORDER BY SubTotal DESC
go

SELECT * FROM dbo.OrderSubTotalsViewNOTOrdered 
ORDER BY SubTotal DESC
go

SELECT * FROM dbo.OrderSubTotalsViewOrdered2
go

SELECT * FROM dbo.OrderSubTotalsViewOrdered3
go

-- No difference between these two... and if you're ONLY using this
-- to get a displayed set then you're OK BUT, I really want to 
-- STRONGLY urge you to use the ORDER BY when selecting from the view
-- AND NOT use this hint inside of a VIEW. In a lot of cases, this can
-- cause you excessive and unnecessary overhead.

-- Basically, if this view becomes relied upon for other uses, then
-- this ORDER BY will begin to cause you problems.

-- Compare the plans (showplan, Ctrl+k) of the two following 
-- queries and you'll see that the view which has the ORDER BY
-- in it has an extra step of ordering the data - which is NOT
-- necessary.

SELECT C.ContactName, Sub.* 
FROM OrderSubTotalsViewOrdered2 AS Sub
	JOIN Orders AS O ON O.orderid = Sub.Orderid
	JOIN Customers AS c ON c.customerid = o.customerid
WHERE C.City = 'Madrid'
ORDER BY SubTotal DESC 
go

SELECT C.ContactName, Sub.* 
FROM OrderSubTotalsViewNOTOrdered AS Sub
	JOIN Orders AS O ON O.orderid = Sub.Orderid
	JOIN Customers AS c ON c.customerid = o.customerid
WHERE C.City = 'Madrid'
ORDER BY SubTotal DESC
go