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.
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.
|
1 2 3 4 5 6 7 8 |
USE master; IF DB_ID(N'SchemaTest') IS NOT NULL BEGIN ALTER DATABASE SchemaTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE SChemaTest; END CREATE DATABASE SchemaTest; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
USE SchemaTest; GO CREATE SCHEMA Schema1 CREATE TABLE Table1 ( row_data varchar(25) NOT NULL PRIMARY KEY CLUSTERED ); GO CREATE SCHEMA Schema2 CREATE TABLE Table1 ( row_data varchar(25) NOT NULL PRIMARY KEY CLUSTERED ); GO CREATE TABLE dbo.Table1 ( row_data varchar(25) NOT NULL PRIMARY KEY CLUSTERED ); |
We’ll insert a single row into each table so we can easily identify which table appears in our “search” results later.
|
1 2 3 4 5 6 7 8 |
INSERT INTO dbo.Table1 (row_data) VALUES ('dbo.Table1'); INSERT INTO Schema1.Table1 (row_data) VALUES ('Schema1.Table1'); INSERT INTO Schema2.Table1 (row_data) VALUES ('Schema2.Table1'); |
This query shows the contents of each row, along with the table name:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
;WITH src AS ( SELECT t.row_data , rd.partition_id FROM dbo.Table1 t CROSS APPLY sys.fn_RowDumpCracker(%%ROWDUMP%%) rd UNION ALL SELECT t.row_data , rd.partition_id FROM Schema1.Table1 t CROSS APPLY sys.fn_RowDumpCracker(%%ROWDUMP%%) rd UNION ALL SELECT t.row_data , rd.partition_id FROM Schema2.Table1 t CROSS APPLY sys.fn_RowDumpCracker(%%ROWDUMP%%) rd ) SELECT TableName = s.name + N'.' + o.name , src.row_data FROM src INNER JOIN sys.partitions p ON src.partition_id = p.partition_id INNER JOIN sys.objects o ON p.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id; |
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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE USER Schema1User WITHOUT LOGIN WITH DEFAULT_SCHEMA = Schema1; CREATE USER Schema2User WITHOUT LOGIN WITH DEFAULT_SCHEMA = Schema2; GRANT SELECT TO Schema1User; GRANT SELECT TO Schema2User; GO |
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.
|
1 2 3 4 5 6 |
EXECUTE AS USER = N'Schema1User'; SELECT * FROM Table1; REVERT GO |
╔════════════════╗ ║ row_data ║ ╠════════════════╣ ║ Schema1.Table1 ║ ╚════════════════╝
Here’s Schema2User doing the exact same SELECT statement:
|
1 2 3 4 5 6 |
EXECUTE AS USER = N'Schema2User'; SELECT * FROM Table1; REVERT GO |
╔════════════════╗ ║ 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:
|
1 2 3 4 5 6 7 |
DROP TABLE Schema1.Table1; GO EXECUTE AS USER = N'Schema1User'; SELECT * FROM Table1; REVERT GO |
╔════════════╗ ║ row_data ║ ╠════════════╣ ║ dbo.Table1 ║ ╚════════════╝
What happens if we subsequently drop Table1 from the dbo schema as well?
|
1 2 3 4 5 6 7 8 |
DROP TABLE dbo.Table1; GO EXECUTE AS USER = N'Schema1User'; SELECT * FROM Table1; REVERT GO |
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:
|
1 2 3 4 5 |
EXECUTE AS USER = N'Schema1User'; SELECT * FROM Schema2.Table1; REVERT GO |
╔════════════════╗ ║ 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.
In addition, it is worth adding how the select * from Table1 statement behaves, if it is called from within a procedure, eg [procSchema]. [MyProcedure].
CREATE SCHEMA procSchema;
CREATE TABLE procSchema.Table1
(row_data varchar (25) NOT NULL
PRIMARY KEY CLUSTERED);
INSERT INTO procSchema.Table1 (row_data)
VALUES (‘procSchema.Table1’);
CREATE PROCEDURE [procSchema]. [MyProcedure]
as begin select * from Table1 end;
execute [procSchema]. [myProcedure];