Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
Thanks!
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.
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.
