Improve the performance of your SQL queries by qualifying object names

In addition to replacing the * wildcard in SELECT statements with an explicit list of column names, fully qualifying all SQL object’s names in SQL queries will boost their performance.

Namely, whenever a query is executed, SQL Server will check its cache to see if its result set can be found there. Now, initially SQL Server will attempt to automatically qualify the objects using a login name. For example, if log on to the SQL instance is done using the login John and the following is executed:

SELECT ID FROM Employee

the SQL Server will actually interpret this as:

SELECT ID FROM John.Employee

Now, if it the object John.Employee doesn’t exist SQL Server will attempt to resolve the object name as dbo.Employee and so on until it finally locates the object the query referred to.

The underlying problem here is actually that SQL Server cannot reuse execution plans if it doesn’t know the owner of the object beforehand, because in that case it doesn’t know which statistics to trust, and it has to resort to recompilation which is an expensive operation on its own; that’s why reusing an existing plan is almost always a good idea. However, SQL Server will need to create a redundant plan which occupies memory. In turn, this leads to additional waste in CPU and I/O resources.

The only solution is to make sure to always fully qualify all of the objects referenced in queries.

Now, here comes the real catch – all of this seems easy enough on paper, but in reality this means that will be needed to often look at scripts where it’s needed to explicitly name 20+ objects and columns. This process is not only tedious, it’s very error-prone and hence, frustrating.

This is where ApexSQL Refactor comes into play.

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in which formats and refactors SQL code using nearly 15 code refactors and over 200 formatting options. It expands wildcard, fully qualifies object names, renames SQL database objects and parameters without breaking dependencies and much more.

To fully qualify all object and column names:

1. Open the SQL script in SSMS or Visual Studio
2. In the Main menu choose ApexSQL Refactor from the ApexSQL menu
3. Select the Qualify object names command:
   

For example:

Before refactoring

SELECT DISTINCT
    ( ReportName ) ,
    DATEPART(hh , StatsDateNow) AS 'Hour' ,
    Day(StatsDateNow) AS 'Day' ,
    Month(StatsDateNow) AS 'Month' ,
    Year(StatsDateNow) AS 'Year'
FROM
    [MyReport] INNER JOIN [MyStats]
ON  StatsReportID = ReportID
ORDER BY
    ReportName ASC

After refactoring

SELECT DISTINCT
    ( MyReport.ReportName ) ,
    DATEPART(hh , MyStats.StatsDateNow) AS 'Hour' ,
    Day(MyStats.StatsDateNow) AS 'Day' ,
    Month(MyStats.StatsDateNow) AS 'Month' ,
    Year(MyStats.StatsDateNow) AS 'Year'
FROM
    dbo.[MyReport] INNER JOIN dbo.[MyStats]
ON  MyStats.StatsReportID = MyReport.ReportID
ORDER BY
    MyReport.ReportName ASC

The Qualify object names code refactor of ApexSQL Refactor is an effective way to improve SQL query performance as well as improving the quality and readability of SQL code.