(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

Issue: October 2002
Section: Database Design
Author: Jeff Jones

T-SQL	Fat Isn't Always Phat
Listing 1	This two-dimensional JOINed SQL is written in ANSI-compatible T-SQL that returns all the necessary data. However, the resulting rowset contains a high percentage of duplicated information.  Iterating through the data to distinguish Orders and Products requires a lot of comparison of the duplicated data. Removing duplication and relating data hierarchically makes the result smaller and easier to use.

CREATE PROCEDURE sp_FatCustomerOrderDetail
	@Country nvarchar(15), 
	@BeginDate datetime, 
	@EndDate datetime 
AS
	SELECT Customers.CompanyName, 
		Customers.ContactName, 
		Customers.ContactTitle, Customers.Address, 
		Customers.City, Customers.Region, 
		Customers.PostalCode, Customers.Country, 
		Customers.Phone, Customers.Fax, 
		Orders.OrderID, Orders.CustomerID, 
		Orders.EmployeeID, Orders.OrderDate, 
		Orders.RequiredDate, Orders.ShippedDate, 
		Orders.ShipVia, Orders.Freight, 
		Orders.ShipName, Orders.ShipAddress, 
		Orders.ShipCity, Orders.ShipRegion, 
		Orders.ShipPostalCode, Orders.ShipCountry, 
		[Order Details].ProductID, 
		Products.ProductName, 
		[Order Details].Quantity, 
		[Order Details].Discount
FROM Orders 
	INNER JOIN [Order Details] ON 
		Orders.OrderID = [Order Details].OrderID 
	INNER JOIN Products ON 
		[Order Details].ProductID = 
			Products.ProductID 
	INNER JOIN Customers ON 
		Orders.CustomerID = Customers.CustomerID
WHERE (Orders.OrderDate 
		BETWEEN @BeginDate AND @EndDate) AND 
		(Customers.Country =@Country)
ORDER BY 
	Customers.CompanyName, Orders.OrderDate DESC
