SQL Server has a capability to create many database objects with the same name. But they must belong to different schema. Let’s look how we can deal with this.
My simple test case:
- Create a new schema tst
- Create a new login test
- Create a new database user test for that login and assign schema tst to him
- Make the user test to be the owner of schema tst
- Create two table with the same name in dbo and tst schema
- Insert different values to the correspondent tables
Script can be downloaded from db_object_names_resolution
This time we run SELECT command without schema name. What should we see? ‘dbo’ or ‘tst’?
The answer is ‘dbo’.
Try to change execution context to user test and run the same command.
We’ve just got the results from the table in tst schema. Why?
If you look at the User Mapping in the Login Properties window, you see the user and default schema for that particular login.
Do you understand what happens?
When I connect to SQL Server as Administrator, I run the query as user dbo and have a default schema dbo. The results are taken from [dbo].[Clients].
When I connect as test, I run the query as user test, so the default schema for that user is tst. The results are taken from [tst].[Clients].
The next question – what will be if the user test run the query from table in dbo schema without denoting the schema name? There are 2 possible answers: the query will return the results from that table or will not be executed. What do you think about that?
To sum up, I would recommend to use unique names for database objects even if they belong to different schema.
Hint: the first search to resolve the database object’s name is made in user default schema, then in dbo schema.