quote:

---

Bookmark Lookup

The Bookmark Lookup logical and physical operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, then the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.

---

DROP TABLE Orders
DROP TABLE Customers

CREATE TABLE Customers (CustomerID int PRIMARY KEY, "Name" varchar(50) NOT NULL)

CREATE TABLE Orders (CustomerID int NOT NULL REFERENCES Customers(CustomerID),
  OrderID int NOT NULL, "Date" datetime NOT NULL, Amount money NOT NULL,
  PRIMARY KEY (CustomerID, OrderID)
)

INSERT INTO Customers
SELECT n, CAST(NEWID() AS varchar(50))
FROM Numbers
WHERE n < 10

INSERT INTO Orders
SELECT N1.n, N2.n,
  DATEADD(s, 86400*CAST(N2.n AS float) + 43200*RAND(CAST(NEWID() AS binary(4))), '20000101'),
  RAND(CAST(NEWID() AS binary(4)))*1000.0
FROM Numbers AS N1, Numbers AS N2
WHERE N1.n < 10
  AND N2.n < 1000

-- A
SELECT Name, SUM(Amount) as TotalAmount
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
-- I presume Jeff didn't really mean C.CustomerID = O.OrderID
GROUP BY C.Name

-- B
SELECT C.Name, O.TotalAmount
FROM Customers C
INNER JOIN (SELECT CustomerID, SUM(Amount) as TotalAmount FROM Orders GROUP BY CustomerID) O
ON C.CustomerID = O.CustomerID

-- C
SELECT Name, SUM(Amount) as TotalAmount
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.Name