When you`re creating a scalar function you define the return type
CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500)
AS
BEGIN
RETURN (SELECT dbo.usernames(@user_id))
END
GO
Which was NVARCHAR(500) in this case and SQL server does not need to check the underlying structure of referenced functions/tables as long as knows the return type.
It will fail to execute tho, if usernames function (in this case) would return something other than NVARCHAR(500), which is expected
On the other hand
CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE
AS
RETURN
(SELECT dbo.usernames(@user_id) as username)
GO
As specified ,returns table therefore SQL server checks the underlying structure of the table because it needs to know what kind of table is begin returned. How many columns it has,if any of the columns are without names(in aggregate cases) and inform you about it. You can test this by running the query above without referencing the column name( 'as user name' ) in case username table does not have a specified column name.
Note: I had to tweak this two functions a little bit, to make them work.
Hope this clarifies what is going under the hood for you