Best practices for T-SQL querying

There are a number of best practices for writing good T-SQL that don’t constitute a pattern or anti-pattern, which is something we will discuss next in this chapter, but are important enough to observe when we want to write good queries. This section covers those practices.

Referencing objects

Always reference objects by their two-part name (<schema>.<name>) in T-SQL code because not doing so has some performance implications.

Using two-part object names prevents name resolution delays during query compilation: if the default schema for a user connecting to the SQL Database Engine is HumanResources, and that user attempts to execute the stored procedure dbo.uspGetEmployeeManagers for which it also has permissions, but simply references uspGetEmployeeManagers, the SQL Database Engine first searches the HumanResources schema for that stored procedure before searching other schemas, thus delaying resolution and therefore execution...