9

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!

|improve this question
12

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.

|improve this answer
  • Then why it's different with SPs. As you said it may be to avoid naming collisions. 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. – Rajesh Ranjan Jul 6 '16 at 10:51
  • 1
    @rajeshRajan Since you have sp_procname(you prefixed your procedure with SP) this forces SQL Server to look first into master database for the compiled plan and since this proc exists in master database will be executed not your, if it was not to find the proc in master then it would have executed yours. You should never create proc with prefix sp_ because it has performance issues. – Shanky Jul 6 '16 at 11:12
  • I think Shanky answered the "why" question directly when he mentioned it was set by the SQL Server development team. You could ask why they did so and why the behavior of functions is inconsistent with procedures, but the answer probably doesn't matter too much. – Michael J Swart Sep 26 '16 at 14:07
  • 1
    BTW, the performance impact of procedures that start with "sp_" can't be measured. It hasn't been an issue for years. The sp prefix still might not be a good idea, but performance isn't the reason. – Michael J Swart Sep 26 '16 at 14:09
9

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.

|improve this answer
  • Then why it's different with SPs. As you said it may be to avoid naming collisions. 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. – Rajesh Ranjan Jul 6 '16 at 10:48
  • 3
    @Rajesh. Objects beginning sp_ are special cased to always look in the master/resource database. And it is documented that this prefix should be avoided. There is no such convention for built in functions. – Martin Smith Jul 6 '16 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.