bphipps-931675 (6/21/2011)
Why bother with the temp table? Why not just imbed the select into your where clause?
SELECT
SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson
, COUNT(*) AS Orders
, SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
-- The dynamic IN predicate.
AND SO.SalesPersonID IN (SELECT CONVERT(INT, Item) AS SalesPersonID
FROM dbo.DelimitedSplit8K(@SalesPeople, ','))
GROUP BY
SO.SalesPersonID, C.FirstName, C.LastName
It very well could have been done that way. The main reason I'm using a temp table, aside from it being easy to read and people are used to them, was that temp tables have statistics. I also wanted to demonstrate the the various ways it could be done easily and thought going to a temp table would be easier to read and understand in the various scenarios.
Kevin pointed out that the CTE version would probably not scale very well since CTE's don't have statistics. I'm not sure that a table valued function is really any better than a CTE on creating statistics. I simply don't know. Maybe some guru like Paul White could comment on this.
At the very least, I do know that temp tables have statistics and that a long array that would produce many records in the temp table would scale upwards properly. In fact if it got huge, it could be indexed if it came to that. I don't normally index temp tables unless I get more back in performance than I lose in the indexing phase, however.
Todd Fifield