SQL Server performance and fully qualified table names

Question

It seems to be fairly accepted that including the schema owner in the query increases db performance, e.g.:

SELECT x FROM [dbo].Foo vs SELECT x FROM Foo.

This is supposed to save a lookup, because SQL Server will otherwise look for a Foo table belonging to the user in the connection context.

Today I was told that always including the database name improves the performance the same way, even if you are querying the database you selected in your connection string:

SELECT x FROM MyDatabase.[dbo].Foo

Is there any truth to this? Does this make sense as a coding standard? Does any of this (even the first example) translate to measurable benefits?

Are we talking about a few cycles for an extra dictionary lookup on the database server vs more bloated SQL and extra concatenation on the web server (or other client)?

Answer 1

One thing to keep in mind is that this is a compilation binding, not an execution one. So if you execute the same query 1 million times, only the first execution will 'hit' the look up time, the rest will reuse the same plan and plans are pre-bound (names are already resolved to object ids).

Answer 2

In this case I would personally prefer readability over the tiny performance increase that this could possibly cause, if any.

SELECT * FROM Foo 

Seems a lot easier to scan than:

SELECT * FROM MyDatabase.[dbo].Foo