Sql-server – Why do user defined scalar functions require the schema

functionsschemasql server

When creating a user defined table function, or a user defined procedure, you can call the function or procedure as named.

However, a user defined scalar function requires that you call it with the schema:

select dbo.doit(data);

I have not come across that behaviour in other DBMS. Why is this the case for scalar functions but not for the rest?

This isn’t just morbid curiosity, and I know asking why a Microsoft product is the way it is is unproductive.

If there is a technical reason why a schema is required for scalar functions but not for others, it is worth knowing. This is not the behaviour of other DBMS, but there may be a technical reason why it is the case here.

Best Answer

Because that's what Sybase/Microsoft decided when they implemented it. - a-horse-with-no-name

I think when you call any function without schema it only looks for system function or in-built functions, and if you do a built-in function with schema SELECT dbo.getdate() it only looks in user-defined functions. - biju-jose

The question is why with scalar functions and not the rest?

Probably because when scalar functions were introduced to the product, they could enforce that from the beginning. They couldn't go back and enforce it for other objects because of the amount of existing code that would break. The same reason they've announced deprecation for statements without terminators but in reality that will probably never happen. Remember, too, that the schema prefix wasn't always the schema, it used to indicate the owner.

I was going to offer that it was necessary for the parser to differentiate your intentions in case you create a scalar function that shares a name with a (future) built-in function. Consider SELECT doit() in SQL Server 2017 and then in SQL Server 2021 they add an internal function called doit().

In other cases (table-valued functions), the parser gets more hints about what it is because it is after FROM. But this doesn't hold true anymore, because in the case of STRING_SPLIT() - added in 2016 - if I had a function called string_split() I might be surprised about any behavior change.

You could ask all sorts of "why" questions. Why do some ALTER DATABASE commands have SET <feature> ON and others are SET <feature> = ON? Why do we have both NOWAIT and NO_WAIT? BACKUP TO NUL instead of NULL? All kinds of historical things exist, check-ins with no review, left hand/right hand. The why is usually going to be irrelevant, unknown, or unsatisfactory.

In any case, does "why" really matter? What technical problem does knowing solve? We can't tell you why, you have to ask Microsoft. - aaron-bertrand

Side note: Even though you could omit the schema name for most objects, it is a better practice (when using SQL Server) to add the schema prefix to all objects used. You might see some unexpected or unwanted behaviour when you don't. A blog post about this: Bad habits to kick : avoiding the schema prefix by Aaron Bertrand - randi-vertongen

Related Question