Inconsistent naming convention

The first thing you're faced with when you switch from Oracle to SQL Server is object naming convention. There are books about how those database work. The whole topic would cover a lot of areas such as administration, access control and what not, so in big points, no details.
In Oracle world you have database instances that don't know anything about each other. Inside such instance you have schemas and you generally work within their boundaries. Since schema is basically an user there's very limited interaction between them (some preivileges are required). In MS SQL Server however there might be multiple databases existing at the same time and schemas, though existing, are merely a logical separators.

In Oracle you're strictly limited to 1 and 2-part naming conventions:
[SCHEMA.]<OBJECT>
where [SCHEMA.] is not obligatory and if omitted CURRENT_SCHEMA is used (CURRENT_SCHEMA is a system variable and it generally works as MS SQL's USE statement, by default CURRENT_SCHEMA is a logged user name). That's it.

In MS SQL Server, however, you can use one of: 1, 2, 3 and 4-part naming conventions:
[INSTANCE.][DATABASE.][SCHEMA.]<OBJECT>
It's pretty self explanatory. One note - in case of SQL Server default [SCHEMA] is called dbo. In case of 3 and 4-part naming conventions you can still omit the schema name when pointing to objects in dbo by using a so called double-dot convention (just remove dbo from the query).

Each of the following would lead to the same table:

use master
go
select * from SaleTotals
go
select * from dbo.SaleTotals
go
select * from master.dbo.SaleTotals
go
select * from master..SaleTotals
go
select * from dbserver.master.dbo.SaleTotals
go

It's not so much different to the Oracle. It certainly makes sense. No issues about it whatsoever from my side.

My issue is that SQL Server is very inconsistent when it comes to enforcing these conventions. This mainly relates to DDL operations and... yes, you guessed it: functions.

So, what did I expect? I expected all those conventions can be used in all situations, but no. They apply only to the select statement and not in it's entirety - minus function calls of course ;)

How about DDL then?

create table DropMe1 ( x int )
go
-- Commands completed successfully.
create table dbo.DropMe2 ( x int )
go
-- Commands completed successfully.
create table master.dbo.DropMe3 ( x int )
go
-- Commands completed successfully.
create table dbserver.master.dbo.DropMe4 ( x int )
go
Msg 117, Level 15, State 1, Line 168
The object name 'dbserver.master.dbo.DropMe4' contains more than the maximum number of prefixes.
The maximum is 2.

Ok. Inconsistent? Yes, but that's fine. You can't access objects from another server directly anyway so it's not a problem at all. Strange they thought of 4-part naming convention anyway but I'm going to pass it and not bother about it ever again.

Let's try something else, shall we?

use master
go
create view DropMe_V1 as select 1 as x
go
-- Commands completed successfully.
create view dbo.DropMe_V2 as select 1 as x
go
-- Commands completed successfully.
create view master.dbo.DropMe_V3 as select 1 as x
go
Msg 166, Level 15, State 1, Line 177
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

What? You can't create view using 3-part naming convention? Duh... Didn't expect that (as a matter of fact - I did, I already knew it would happen ;D). It's very common for other DDL commands, including functions. We need one to demonstrate another example of such inconsistency.

Say we have the following:

create or alter function dbo.DropMeF () 
returns int as 
begin
    return 1
end
go

Let's try using it.

select master.dbo.DropMeF () as fresult;

fresult
-----------
1

Ok, now this:

select dbo.DropMeF () as fresult;

fresult
-----------
1

So far so good, now this:

select DropMeF () as fresult;

Msg 195, Level 15, State 10, Line 196
'DropMeF' is not a recognized built-in function name.

Oh... bummer. It appears to me as Microsoft decided that schema prefix is mandatory as not to confuse UDFs with built-in functions. I suspect the MS developers have a strong affection towards conditional if and case statements (that suspicion is a topic for another blog post). This is bollocks to me. I would rather see a form of object already exist when accidently trying to overwrite system function instead of stumbling on something like this.

After this we already can expect something strange when using double-dot, can't we?

select master..DropMeF () as fresult;

Msg 102, Level 15, State 1, Line 199
Incorrect syntax near '.'.

Of course! Since we already assumed they enforce schema name in case of function call so it's no different in this case. Still stupid.

Do you know any other (different) cases of such inconsistency in SQL Server?

Best