CODEWITHSUNDEEP
sql-server
Jim
Jim

Reputation: 67

Which table is used when exists in multiple schema's, but no clarification in SELECT?

I am trying to find this answer and it is not in any documentation I can find, and I have not attempted to create this and test on our SQL Server as I am afraid this scenario already exists, and I could break some reporting.

Singe table name:

Dude

Multiple Schemas: 

dbo.dude
arch.dude

If a SELECT statement is ran with no schema, what happens?

Select * from Dude;

Will a SELECT statement, with existing tables in two Schema's, use the arch.dude table in some alphanumeric sort or will it just fail?

I know this is not a "deep" question, but helpful in my quest to instruct others as to why they want to add the schema to the front of their table names in a query.

Upvotes: 1

Views: 165

Answers (1)

DavidG
DavidG

Reputation: 119016

It will use the users default schema (which is usually dbo). You can change the default like this:

ALTER USER SomeUser
WITH DEFAULT_SCHEMA = SomeSchema

Or using the SSMS interface:

enter image description here

Upvotes: 3

Related Questions