SQL Server performance and fully qualified table names
performance, sql-server
Solution
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).
Problem
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)?