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
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
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