Is dbo the default schema when querying if it is not specified?

If I query a table which exists in two different schemas but has the same name what determines which table is used?

For example I have table Bar which exists in both dbo and foo, if I execute the statement below which one is returned:

SELECT * FROM Bar

I thought this was determined by the users default schema but doesn't appear to be the case.

I ask because I have found queries written by someone else who is not specifying the schema name yet the database being queried has the same tables in different schemas for ETL.

According to the Books Online:


The value of DEFAULT_SCHEMA is ignored if the user is a member of the
sysadmin fixed server role. All members of the sysadmin fixed server
role have a default schema of dbo.

So it seems the account used where the default schema is not working is a sysadmin role member. The default schema of the dbo user is always dbo and cannot be changed.

The best practice is generally is to schema-qualify object references to avoid ambiguity and improve performance.

StackExchange Database Administrators Q#157070, answer score: 6