Why is the schema prefix (dbo) mandatory when we call a function?

When user is mapped with default schema (dbo) and we can select all tables under [dbo] without prefixing the schema.

We can execute stored procedures without prefix if it's under the default schema.

Given that, why do we need to prefix a function with the schema?

The other answer explains that this is a restriction but not the reason why.

The requirement is not always true. Scalar UDFs can be EXEC-ed and still use implicit resolution (example).

I imagine this is to avoid naming collisions.

If functions were allowed to be referenced without schema then someone that created their own function that happened to be called crypt_gen_random in 2000 or 2005 would encounter problems upgrading to a later version as this became the name of a built in function in 2008.

There is no ambiguity with the exec usage as built in functions can't be called like that.

I just created a SP "create procedure sp_help
as select getdate()" in my user database and when I execute with or without schema (dbo), SQL Server refers to system SP. Why it does not execute my SP that I have created?

Objects beginning sp_ are special cased to always look in the master/resource database. It is documented that this prefix should be avoided. There is no such convention for built in functions.

StackExchange Database Administrators Q#142984, answer score: 12