Sunday, May 1, 2011

Object Name Resolution – SQL Server

Understanding how SQL Server resolves objects’ names will be an effective remedy against performance issues. This name resolution happens if securables are not fully qualified. A statement like SELECT SalesOrderID FROM SalesOrderHeader will be a candidate for this. As you see, the SalesOrderHeader is not written as a fully qualified name, hence SQL Server tries to resolve it by adding Schema owned by user connected. If SQL Server cannot find an object like ConnectedUser’sSchema.SalesOrderHeader then it tries to find an object called dbo.SalesOrderHeader as the second step. Error is thrown, if it is not successful too. Here is an example for it;

USE AdventureWorks2008
GO
 
-- creating a new table with dbo schema
SELECT TOP (1) * 
INTO SalesOrderHeader
FROM Sales.SalesOrderHeader
 
-- Create two logins for testing
CREATE LOGIN HR_Manager WITH PASSWORD = '123'
    , CHECK_POLICY = OFF;
CREATE LOGIN Sales_Manager WITH PASSWORD = '123'
    , CHECK_POLICY = OFF;
GO
 
-- Adding them as users with default schemas
CREATE USER HR_Manager WITH DEFAULT_SCHEMA = HumanResources
CREATE USER Sales_Manager WITH DEFAULT_SCHEMA = Sales
GO
 
-- Given users permission on data reading
sp_addrolemember 'db_datareader', 'HR_Manager';
sp_addrolemember 'db_datareader', 'Sales_Manager';
 
-- Execute as HT_Manager and see
EXECUTE AS USER = 'HR_Manager'
GO
-- This shows records from newly created
-- table, not from original table
SELECT * FROM SalesOrderHeader
-- This throws an error
SELECT * FROM SalesOrderDetail
 
REVERT
GO
 
-- Execute as Sales_Manager
EXECUTE AS USER = 'Sales_Manager'
GO
-- Both statements will work
SELECT * FROM SalesOrderHeader
SELECT * FROM SalesOrderDetail
 
REVERT
GO

As you see with the code, when HR_Manager executes SELECT statements, as the first step, names of them will be resolved as HumanResources.SalesOrderHeader and HumanResources.SalesOrderDetail. SQL Server does not find any objects and do the next step, resulting dbo.SalesOrderHeader and dbo.SalesOrderDetail. A match will be found for first one as we have created one with dbo schema but not for the second. In that case, second statement throws an error.

When Sales_Manager executes, SQL Server resolves objects’ names as Sales.SalesOrderHeader and Sales.SalesOrderDetail. In that case, both statements are successfully executed.

NameResolution

Even though Sales_Manager executes statements without any error, it would be always better to make securables as fully qualified objects, that helps SQL Server to quickly execute the code without performing an additional task.

No comments:

Post a Comment