CODEWITHSUNDEEP
sql-servert-sql
Green Grasso Holm
Green Grasso Holm

Reputation: 695

Schema is required in SQL Server to invoke user-defined functions

I've always found that when I'm programming in SQL Server, in a database where everything I create is under the dbo schema, that I don't need to reference the schema when querying tables or views or executing stored procedures, but I always need to prefix invocations of user-defined functions with dbo. or else the parser fails to recognize the name. Why is that?

Upvotes: 3

Views: 333

Answers (2)

Martin Smith
Martin Smith

Reputation: 453278

It is so that it does not clash with (current or future) built in functions.

If you had a function called dbo.Least and were allowed to call it as Least then you would get a conflict when moving up to SQL Server 2022 as that suddenly has a built in function called LEAST.

There is no sp_ convention for built in function names to indicate an area of names "reserved by Microsoft"

If you EXEC the user defined function (rather than SELECT it) you can still call it without schema qualification as there is no possibility of ambiguity there.

Upvotes: 7

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

I always need to prefix invocations of user-defined functions with dbo. or else the parser fails to recognize the name. Why is that?

No good reason. It's just a technical limitation of the parser.

Upvotes: 1

Related Questions