I enjoyed the look at the various method of data access you compared. Thanks for taking the time to put this together.

I do want to comment on your assessment that derived tables and common table expressions are "essentially the same thing." While it may appear so, these two solutions are very different in functionality. A derived table can be used once only in an outer query, while a CTE can be used multiple times and can be used recursively, providing significantly more functionality. For example, the query could return not only the names but a subtotal count of the number of females by role:

;WITHFemales

AS(

SELECTDirectorName AS PersonName,

'Director' AS Job,

DirectorDob AS Dob

FROMtblDirector

WHEREDirectorGender = 'Female'

UNION

SELECTActorName AS PersonName,

'Actor' AS Job,

ActorDob AS Dob

FROMtblActor

WHEREActorGender = 'Female'

)

SELECTJob, PersonName

FROM(

SELECTJob, 1 AS SortSeq, PersonName

FROMFemales

UNION

SELECT'Total for ' + Job AS Job, 2 AS SortSeq, CAST(COUNT(PersonName) AS VARCHAR(10)) AS PersonName

FROMFemales

GROUP BY 'Total for ' + Job

) x

ORDER BY Job, SortSeq, PersonName;

(I'm not at a SQL Server window at the moment, so I have not tested the code above.)

Please notice that the "Females" CTE has been used twice in the outer query, once to list the details and once to provide summary records by job description, something that (to my knowledge...) is not possible with a derived table.