CREATE OR ALTER FUNCTION dbo.CountHistoryRowsForProduct
    (@ProductID integer)
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bigint;

    SELECT @r = COUNT_BIG(*)
    FROM Production.TransactionHistory AS TH
    WHERE TH.ProductID = @ProductID;

    RETURN @r;
END;

SELECT
    P.ProductID,
    HistoryCount = dbo.CountHistoryRowsForProduct(P.ProductID)
FROM Production.Product AS P;

CREATE OR ALTER FUNCTION dbo.CountHistoryRowsForProduct
    (@ProductID integer)
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
    RETURN
    (
        SELECT COUNT_BIG(*)
        FROM Production.TransactionHistory AS TH
        WHERE TH.ProductID = @ProductID
    );
END;