Does writing the full path in SELECT statements enhance performance SQL?

Question

Is the performance of queries impacted when writing the full query path. And what is the best practice when writing such queries ? Assuming the script is way more complex and longer than the following.

Example #1:

SELECT Databasename.Tablename.NameofColumn
FROM databasename.tablename

Example #2:

SELECT NameofColumn
FROM tablename

OR using aliases - example #3:

SELECT t.NameofColumn
FROM tablename t

Answer 1

There are a number of considerations when you're writing queries that are going to be released into a production environment, and how and when to use fully qualified names is one of those considerations.

A fully qualified table name has four parts: [Server].[Database].[Schema].[Table]. You missed Schema in your examples above, but it's actually the one that makes the most difference. SQL Server will allow you to have objects with the same name in different schemas; so you could have dbo.myTable and staging.myTable in the same database. SQL Server doesn't care, but your query probably does.

Even if there aren't identically named objects, adding the schema still helps the engine find the object you're querying a little bit faster, so there's your performance boost, albeit a small one, and only in the compile/execution plan step.

Besides performance, though, you need to worry about readability for your own sake when you need to revisit your code, and conventionality for when somebody else needs to look at your code. Conventions vary slightly from shop to shop, but here are a couple of generalities that will at least make your code easier to look at, say, on Stack Overflow.

1. Use table aliases.

This gets almost unreadable after about three column names:

SELECT 
  SchemaName.Tablename.NameofColumn1,
  SchemaName.Tablename.NameofColumn2,
  SchemaName.Tablename.NameofColumn3
FROM SchemaName.TableName

This is just easier on the brain:

SELECT 
  tn.NameofColumn1,
  tn.NameofColumn2,
  tn.NameofColumn3
FROM SchemaName.TableName as tn 

2. Put the alias in front of every column reference, everywhere in your query.

There should never be any ambiguity about which table a particular column is coming from, either for you, when you're trying to troubleshoot it at 3:00 AM, or for anyone else, when you're sipping margaritas on the beach and your buddy's on call for you.

3. Make your aliases meaningful.

Again, it's about keeping things straight in your head later on. Aaron Bertrand wrote the definitive post on it almost ten years ago now.

4. Include the database name in the FROM clause if you want, but...*

If you have to restore a database using a different name, your procedures won't run. In my shop, we prefer a USE statement at the top of each proc. Fewer places to change a name if need be.

tl;dr

Your example #3 is pretty close. Just add the table schema to the FROM clause.