1

Edit: I'm aware that SELECT * is bad practice, but it's used here just to focus the example SQL on the table statement rather than the rest of the query. Mentally exchange it for some column names if you prefer.

Given a database server MyServer (which we are presently connected to in SSMS), with several databases MyDb1, MyDb2, MyDb3 etc and default schema dbo, are any of the following equivilant queries (they will all return exactly the same result set) more "optimal" than the others?

SELECT * FROM MyServer.MyDb1.dbo.MyTable

I was told that this method (explicitly providing the full database name including server name) treats MyServer as a linked server and causes the query to run slower. Is this true?

SELECT * FROM MyDb1.dbo.MyTable

The server name isn't required as we're already connected to it, but would this run 'faster' than the above?

USE MyDb1
GO

SELECT * FROM dbo.MyTable

State the database we're using initially. I can't imagine that this is any better than the previous for a single query, but would it be more optimal for subsequent queries on the same database (ie, if we had more SELECT statements in the same format below this)?

USE MyDb1
GO

SELECT * FROM MyTable

As above, but omitting the default schema. I don't think this makes any difference. Does it?

Community
  • 1
  • 1
Kai
  • 2,050
  • 8
  • 28
  • 46
  • If this is your performance bottleneck, I'm remarkably surprised. I/O costs should normally swamp this (very small) part of query compilation. That being said, you might find [this answer](http://dba.stackexchange.com/a/18770/6381) an interesting read for your final two. – Damien_The_Unbeliever Sep 20 '13 at 09:59
  • It is not. I was just curious about what impact it actually has, if any (most particularly the first case with the explicit server name). I suspected none to very little, but it would be nice to know if there is a difference. – Kai Sep 20 '13 at 10:02
  • 1
    Theoretically using fully qualified names should be faster, as you explicitly tell SQL Server which server/database/schema the table you're looking for is in. Most likely the delay would be negligible, and prtetty hard to measure unless you build some routine that executes the queries hundreds of times in a row. As for adding the servername, I'm not convinced it'd make things slower, unless SQL Server would somehow start looking for login credentials. I'd like to think it's smarter than that, but never bothered testing it. – SchmitzIT Sep 20 '13 at 10:22
  • 1
    The difference between `MyDB.MySchema.MyTable` and `USE MyDB`, followed by `Select from MySchema.MyTable` is mostly relevant when you have subsequent other queries. The former allows you to easily pull data when you're in the context of another database, while the latter means less typing in case you're only extracting data from a single database. – SchmitzIT Sep 20 '13 at 10:24
  • 1
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/21fd0b9d-a096-4197-b3d3-9b9ead871878/fully-qualified-names – HLGEM Sep 20 '13 at 19:11
  • Excellent, that about covers everything, particularly the linked server stuff in the link. – Kai Sep 21 '13 at 22:35

1 Answers1

0

SQL Server will always look for the objects you sepcify within the current "Context" if you do not specify a fully qualified name.

Is one faster than the other, sure, the same as a file name on your hard drive of "This is a really long name for a file but at long as it is under 254 it is ok.txt" will take up more hard-drive (toc) space than "x.txt". Will you ever notice it, no!

As far as the "USE" keyword, this just sets the context for you, so you dont have to fully qualify object names. The "USE" keyword is NOT sql, you cannot use in in another application (like a vb/c# app) or within a stored procedure but it is like the "GO" keyword in that it tells SSMS to do something, change the context.

Steve
  • 5,585
  • 2
  • 18
  • 32