1. I see no difference in the time when measured properly...

--SELECT GETDATE() as Time

--GO

SET STATISTICS TIME ON

CREATE TABLE #Temp_Example (

[CategoryID] INT NOT NULL,

[Category_Count] INT NOT NULL

)

INSERT INTO #Temp_Example (CategoryID, Category_Count)

SELECT C.CategoryID, COUNT(*) AS Category_Count

FROM Categories C

INNER JOIN Products P ON C.CategoryID = P.CategoryID

GROUP BY C.CategoryID, C.CATEGORYNAME

SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Example.Category_Count

FROM Categories C

INNER JOIN Products P ON C.CategoryID = P.CategoryID

INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID

ORDER BY C.CategoryName

DROP TABLE #Temp_Example

SET STATISTICS TIME OFF

GO

--SELECT GETDATE() as Time

PRINT REPLICATE('=',100)

-- --Duration = 80-110

--

-- ==================

-- The following query uses a derived table.

-- Northwind.dbo.Categories = 8 rows,

-- Northwind.dbo.Products = 77 rows

-- SELECT GETDATE() as Time

-- GO

SET STATISTICS TIME ON

SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count

FROM Categories C

INNER JOIN Products P ON C.CategoryID = P.CategoryID

INNER JOIN (

SELECT C.CategoryID, COUNT(*) AS Category_Count

FROM Categories C

INNER JOIN Products P ON C.CategoryID = P.CategoryID

GROUP BY C.CategoryID, C.CategoryName

)CT ON C.CategoryID = CT.CategoryID

ORDER BY C.CategoryName

SET STATISTICS TIME OFF

-- GO

-- SELECT GETDATE() as Time

--Duration = 50-80

2. There will probably be a little difference in time because you've included an INSERT in the first set. 2nd set only has one operation.

3. The following statement you posted....

By using derived tables instead of temporary tables, you reduce disk I/O thereby improving the application's performance

... is true ONLY if you intend to use the calculation saved in the Temp Table once.

4. For complex joins with aggragates, it is sometimes MUCH cheaper to use your first method even if the results in the Temp table will only be used once. It just didn't turn out that way for your simple example. Divide and Conquer is a powerful and effective method to greatly enhance the performance of queries.