Problem With Duplicate Names of Database Objects And Execution Context in SQL Server

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:

  1. Create a new schema tst
  2. Create a new login test
  3. Create a new database user test for that login and assign schema tst to him
  4. Make the user test to be the owner of schema tst
  5. Create two table with the same name in dbo and tst schema
  6. Insert different values to the correspondent tables

Script can be downloaded from db_object_names_resolution

Now we can check the rows in both tables. That’s alright, we see exactly the same values that were inserted earlier. Pay attention to the fact we use schema names when accessing the tables.
explicit schema names

This time we run SELECT command without schema name. What should we see? ‘dbo’ or ‘tst’?
The answer is ‘dbo’.

implicit dbo

Try to change execution context to user test and run the same command.

implicit tst

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.

user mapping

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.

Leave a Reply

Your email address will not be published. Required fields are marked *