On Default Schemas and “Search Paths”

Default schemas in SQL Server can be a blessing, since they reduce the need to specify the schema when creating DDL statements in T-SQL. However, relying on the default schema when creating DML statements can be problematic. A recent question on dba.stackexchange.com asked “Does T-SQL have a Schema search path?”, similar to PostgreSQL implements the search_path parameter. This post shows how schemas are implemented in SQL Server. We’ll also see why it’s important to always specify the schema when using SQL Server.

This Lute Player, by Niccolo Frangipane, looks like he's happy to not be thinking about schemas

This Lute Player, by Niccolo Frangipane, looks like he’s happy to not be thinking about schemas.

First, we’ll create an empty test database, named SchemaTest. We’ll use this database for testing to ensure no existing objects or schemas get “in the way” of our tests.

Continuing the setup, we’ll create two schemas, Schema1 and Schema2. Both schemas contain an identically named and designed table, Table1. We’ll also create a table Table1 in the dbo schema.

We’ll insert a single row into each table so we can easily identify which table appears in our “search” results later.

This query shows the contents of each row, along with the table name:

The output looks like:

╔════════════════╦════════════════╗
║   TableName    ║    row_data    ║
╠════════════════╬════════════════╣
║ dbo.Table1     ║ dbo.Table1     ║
║ Schema1.Table1 ║ Schema1.Table1 ║
║ Schema2.Table1 ║ Schema2.Table1 ║
╚════════════════╩════════════════╝

Here we’re creating a couple of users in the database, and specifying default schemas for each user. Also, we’ll grant each user the ability to SELECT from any and all schemas:

We can use EXECUTE AS USER to allow us to execute commands in the context of a specific user. First, we’ll execute a SELECT query as Schema1User, then Schema2User, to see what it looks like without specifying the schema.

╔════════════════╗
║    row_data    ║
╠════════════════╣
║ Schema1.Table1 ║
╚════════════════╝

Here’s Schema2User doing the exact same SELECT statement:

╔════════════════╗
║    row_data    ║
╠════════════════╣
║ Schema2.Table1 ║
╚════════════════╝

Since each user has their DEFAULT_SCHEMA specified in the CREATE USER statement, the Table1 object in their default schema is automatically used in the SELECT. However, if you delete a table, then re-run the SELECT statement, SQL Server will use Table1 from the dbo schema:

╔════════════╗
║  row_data  ║
╠════════════╣
║ dbo.Table1 ║
╚════════════╝

What happens if we subsequently drop Table1 from the dbo schema as well?

We see an error:

Msg 208, Level 16, State 1, Line 127
Invalid object name 'Table1'.

If, however, we specify Schema2 in the SELECT statement, we’ll get results:

╔════════════════╗
║    row_data    ║
╠════════════════╣
║ Schema2.Table1 ║
╚════════════════╝

Hopefully this illustrates why you should always specify the schema in T-SQL. If you have any questions, or comments, please feel free to add them below!

You may be interested in our other articles on SQL Server Basics.

Ads by Google, Paying the Rent: