I have two SQL servers, one for production and one for test. Consider the following setup:
Prod: Test:
db_1 db_1
db_2 db_2
Now in db_1 there is a stored procedure that accesses db_2:
spFoo in Prod db_1:
...
select * from db_2.dbo.bar
...
In the test environment, that same procedure also needs to access db_2, but not only that, it needs to access the db_2 located on Prod instead of Test. So on Test the procedure has to look like this:
spFoo in Test db_1:
...
select * from Prod.db_2.dbo.bar
...
My question:
Since both scripts want to access the database on Prod, but only the version on Test needs to qualify the name by prefixing the server name, can I leave the prefix in place also for prod, i.e. changing the version on prod into this:
spFoo in Prod db_1:
...
select * from Prod.db_2.dbo.bar
...
Since on Prod, the name Prod.db_2.dbo.bar would reference a database on the same machine, my fantasy is that no proper name lookup will take place, i.e. SQL will know that Prod is the same machine as itself and doesn't have to take the route out to do DNS lookup and/or authentication etc..
Or is this just fantasy? Will it affect performance on Prod to prefix a database name with Prod?
Long story short it won't affect performance
I run a simple test on my machine. I created table tmp. It has 100 random numbers. In the first query i explicitly reference the local server.
select *
from NTB07.Test.dbo.tmp a, NTB07.Test.dbo.tmp b, NTB07.Test.dbo.tmp c
select *
from dbo.tmp a, dbo.tmp b, dbo.tmp c
And checked the execution times for queries are. For the first query
(1000000 rows affected)
SQL Server Execution Times: CPU time = 1501 ms, elapsed time = 8962 ms.
Second Query
(1000000 rows affected)
SQL Server Execution Times: CPU time = 1452 ms, elapsed time = 10128 ms.
So the difference is: 50ms
So as you see there you can specify full path to table :)
I assume that's because when you don't specify the full server name SQL Server adds implicitly table from sys.servers with id=0, which is local one. But its only my guess.