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.
Then why we can call function without prefix (schema), created under dbo?
From Books Online Document on UDF's
Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement. Scalar-valued functions must be invoked by using at least the two-part name of the function.
So this is basically a restriction set by SQL Server development team and I consider it quite correct. Even if it is somehow allowed (just for sake of conversation) I would still use Schema prefix.
I always support using schema name even if it would work without adding it. This is best practice and all "Good" developers use it no matter how much redundant it is.
The other reason what I see is, Database engine needs something to differentiate between system function like getdate () and user defined functions. If you are allowed to call function without schema name how would database engine differentiate between User created function named Getdate or system GETDATE() function.
