Table valued functions can not be granted ‘EXECUTE’ permission

I found an error when I was trying to deploy my database project:

Granted or revoked privilege EXECUTE is not compatible with object on

After quick search on google, I learned that table valued functions can not be granted ‘EXECUTE’ permissions. Instead they need to be granted ‘SELECT’ permissions.

It makes sense and never realised this until I hit this problem. I have been using Table valued and Scaler valued functions and didn’t notice that you use select on table valued functions and execute on scaler valued functions.

Outer Join SQL Catch

Last night I was looking at some SQL code and code similar to below got my attention:

SELECT new.*
FROM #TableA AS new
LEFT JOIN TableB AS old
ON new.[ColId] = old.[ColId]
WHERE old.[ColId] IS NULL

Now TableB has ColId marked as Primary Key. So, I got obsessed trying to figure out the reason behind Where clauses checking a primary field column is null.

Asked to couple of colleagues and they also started scratching head. But more of it, It was me asking about the Where clause, rather than whole query.

I put a question on SQLTEAM and Tara Kizer replied and enlightend me.

—

The query above returns all the rows from #TableA where it does not have matching ColId in TableB. As this query is doing LEFT JOIN, old.[ColId] above will have NULL if it is not matching with new.[ColId].