Sql-server – Why is the schema prefix (dbo) mandatory when we call a function

functionssql server

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!

Best Answer

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.

Related Solutions

Sql-server – Table not getting created under dbo schema

The schema that will be used when schema is omitted will be the default schema of the database user. Therefore, to have dbo schema have the created table without specifying, you'd have to set that database user's default schema to dbo.

Server-wide setting? I don't think so. But you could write a query to convert all database users in a particular database to their default schema changed to dbo if that's what you so choose.

Sql-server – Trying to write a function that doesn’t require dbo. prefix

Using system_function_schema was an undocumented and unsupported process in SQL 2000. As of SQL 2005, it is no longer possible.

By design, UDFs require that the schema be included in the function call.

There is one exception: calling the function like a procedure doesn't actually require the schema, but also wouldn't be terribly useful for your function.

exec trim @input = ' foo '

Additionally, be sure to carefully test queries with scalar functions--they can have significant impact on performance.

Best Answer

Related Solutions

Sql-server – Table not getting created under dbo schema

Sql-server – Trying to write a function that doesn’t require dbo. prefix

Related Question