In this post, we’ll look at why it is important to prefix objects with their schema and look at an example where not prefixing objects with schemas can cause unexpected results. For this post, we’ll use the Northwind database, made available by Microsoft at the Northwind database repository on GitHub. I have made some modifications to the database to support this example, scroll to the Setup section Database Modifications to see them in full. To follow along with the demos, you’ll need to make the database modifications first but they are at the end of the post in order to keep a little air of mystique around what causes the problem (though you could probably argue the title gives it away!)
The Scenario
Let’s say we work for Northwind. The company has a BI analyst called Alice and a DBA called Bob and they work with the Northwind SQL Server database.
Alice has written a query for a report that shows all sales of a product called Chef Anton’s Gumbo Mix, the query is below:
USE Northwind;
SELECT o.OrderID,
o.OrderDate,
d.Quantity,
d.UnitPrice,
p.ProductName
FROM Orders o
JOIN [Order Details] d
ON d.OrderID = o.OrderID
JOIN Products p
ON d.ProductID = p.ProductID
WHERE p.ProductName = 'Chef Anton''s Gumbo Mix'
ORDER BY p.ProductName;
She executes the query and there are no results:
She sends it to Bob, the DBA, because she believes some data has been deleted as she expects some results from this query. The first thing Bob does is execute the query but he sees something different:
Why is Bob seeing different results when he has literally copied and pasted the code and run against the same server? He is confused, but after a few minutes he realises what is happening.
The answer is, Alice has missed something from her code:
FROM Orders o
JOIN [Order Details] d
ON d.OrderID = o.OrderID
JOIN Products p
ON d.ProductID = p.ProductID
Alice hasn’t schema-prefixed her tables and if we look for these objects in the database:
SELECT s.name AS SchemaName,
o.name AS ObjectName,
o.type_desc
FROM sys.objects o
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.name IN ('Orders','Order Details','Products')
ORDER BY o.name,
s.name
We can see that two of these objects have names which are not unique – objects called Products and Orders exist in both the dbo and Reporting schemas.
Alice is a BI Analyst – she uses views created in the Reporting schema to write her reports. The reporting views have some additional logic built in that the BI team want to apply to all reports:
CREATE VIEW Reporting.Products
AS
SELECT [ProductID],
[ProductName],
[SupplierID],
[CategoryID],
[QuantityPerUnit],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder],
[ReorderLevel],
[Discontinued]
FROM dbo.Products
WHERE Discontinued = 0;
GO
CREATE VIEW Reporting.Orders
AS
SELECT [OrderID],
[CustomerID],
[EmployeeID],
[OrderDate],
[RequiredDate],
[ShippedDate],
[ShipVia],
[Freight],
[ShipName],
[ShipAddress],
[ShipCity],
[ShipRegion],
[ShipPostalCode],
[ShipCountry]
FROM dbo.Orders
WHERE Deleted = 0;
We can see there is a WHERE clause in both of these views filtering out soft deleted / discontinued records.
If we look at the configuration of both Alice and Bob’s accounts:
SELECT *
FROM sys.database_principals
WHERE name IN ('Alice','Bob');
We can see that the default schema of both users is different – Alice’s default schema is Reporting, Bob’s default schema is dbo.
As the query that Alice wrote doesn’t prefix the objects, the user’s default schema is used.
Alice’s problem is that in her case, the FROM clause is being bound as
FROM Reporting.Orders o
JOIN Reporting.[Order Details] d
ON d.OrderID = o.OrderID
JOIN Reporting.Products p
and the product she is searching for is discontinued and so is excluded from the Reporting.Products view definition.
Bob sees results as his query is bound to his default schema:
FROM dbo.Orders o
JOIN dbo.[Order Details] d
ON d.OrderID = o.OrderID
JOIN dbo.Products p
ON d.ProductID = p.ProductID
Bob’s default schema is dbo so these objects are the tables and therefore the discontinued products are included.
No Schema Prefixing inside Objects
We’ve seen above what happens if a query isn’t schema-prefixed and the issues it can cause, but what happens when schema-prefixing is missed from within an object?
Let’s say whoever created the view in the reporting schema actually forgot to schema-prefix the table:
CREATE VIEW Reporting.Products
AS
SELECT [ProductID],
[ProductName],
[SupplierID],
[CategoryID],
[QuantityPerUnit],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder],
[ReorderLevel],
[Discontinued]
FROM Products
WHERE Discontinued = 0;
What will be returned for Alice? What will be returned for Bob?
Well actually, we don’t get that far – we get an error when trying to create the object:
Msg 4429, Level 16, State 1, Procedure Products, Line 4 [Batch Start Line 0]
View or function 'Products' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
The message says that the view contains a self-reference and the reason for that is that when creating an object such as a view without schema-prefixing objects inside it, it is assumed that the objects being referred to are in the same schema as the object being created so in this case, SQL Server assumes we are referring to Reporting.Products in the view and the name of the view itself is Reporting.Products, hence the self-reference error message. If we created the view with a different name, without the schema-prefixing like so:
CREATE VIEW Reporting.vwProducts
AS
SELECT [ProductID],
[ProductName],
[SupplierID],
[CategoryID],
[QuantityPerUnit],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder],
[ReorderLevel],
[Discontinued]
FROM Products
WHERE Discontinued = 0;
GO
Products is bound to Reporting.Products as we know from above, SQL Server will resolve unqualified objects to the schema of the module first. If the unqualified object name does not exist in the same schema as the module then it searches the dbo schema.
Setup
Normally in blog posts the setup is at the beginning, but here I have added it to the end as I had to make some changes to the Northwind database so for transparency they are in full below. I could have put this at the beginning of the post but then of course I would have given away the clues to this mystery!
CREATE SCHEMA Reporting;
GO
/* alter the orders table to allow for soft deletes. */
ALTER TABLE dbo.Orders ADD Deleted BIT;
GO
/* set all rows as not deleted */
UPDATE dbo.Orders SET Deleted = 0;
/* soft delete a random order */
UPDATE dbo.Orders
SET Deleted = 1
WHERE OrderId = (
SELECT TOP 1
OrderId
FROM dbo.Orders
ORDER BY NEWID()
);
GO
CREATE VIEW Reporting.Orders
AS
SELECT [OrderID],
[CustomerID],
[EmployeeID],
[OrderDate],
[RequiredDate],
[ShippedDate],
[ShipVia],
[Freight],
[ShipName],
[ShipAddress],
[ShipCity],
[ShipRegion],
[ShipPostalCode],
[ShipCountry]
FROM dbo.Orders
WHERE Deleted = 0;
GO
CREATE VIEW Reporting.Products
AS
SELECT [ProductID],
[ProductName],
[SupplierID],
[CategoryID],
[QuantityPerUnit],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder],
[ReorderLevel],
[Discontinued]
FROM dbo.Products
WHERE Discontinued = 0;
GO
/* create the users */
CREATE LOGIN Alice WITH PASSWORD = '!My57r0ngp455!';
CREATE USER Alice FOR LOGIN Alice;
ALTER USER Alice WITH DEFAULT_SCHEMA = Reporting;
ALTER ROLE db_datareader ADD MEMBER Alice;
CREATE LOGIN Bob WITH PASSWORD = '!My57r0ngp455!';
CREATE USER Bob FOR LOGIN Bob;
ALTER USER Bob WITH DEFAULT_SCHEMA = dbo;
ALTER ROLE db_owner ADD MEMBER Bob;
Conclusion
We have seen the importance of prefixing objects with the schema and we saw the consequences of not doing so could be quite severe and can actually affect results. In this example the difference between the two was so obvious that Alice noticed – zero results vs some results, in real world examples they may be more subtle and go unnoticed.
Don’t be like Alice – remember to schema-prefix objects in your queries!
References / Further Reading
Aaron Bertrand – Bad Habits to Kick: Avoiding the Schema Prefix