Can anyone help me understand the SQL Server execution plan for the following queries?
I expected the subquery version (Query 2) to execute faster, because it's set-based. This appears to be the case when runnning the queries independently - marginally - however the execution plan shows the query costs as 15% vs. 85% respectively:
//-- Query 1 (15%) - Scalar Function
SELECT
gi.GalleryImageId,
gi.FbUserId,
dbo.GetGalleryImageVotesByGalleryImageId(gi.GalleryImageId) AS Votes
FROM
GalleryImage gi
//-- Query 2 (85%) - Subquery
SELECT
gi.GalleryImageId,
gi.FbUserId,
(SELECT COUNT(*) FROM GalleryImageVote WHERE GalleryImageId = gi.GalleryImageId)
FROM
GalleryImage gi
What am I missing here; does the execution plan skip over the cost of the function? Also, any suggestions as to whether either of the above would be better served with a CTE or OVER/PARTITION query?
Thank you in advance!