1

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.

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.