Posted by Erland Sommarskog on 12/29/06 22:32

fireball (fireball@onet.kropka.eu) writes:
> I wihsh to discuss whether to use fully qualified names:
> [database].[schema].object
> of objects to operate (create, query..) on is good or not?

Use two-part names, not three-part names.

Two-part names are particulary important in SQL code outside stored
procedures. If user Joe submits this query:

SELECT col1 FROM dbo.tbl WHERE x = 132

and then user Czeslaw submits this query:

SELECT col1 FROM dbo.tbl WHERE x = 34

the query-plan will be reused (assuming auto-parameterisation). But if
"dbo." is not there, Joe and Czeslaw cannot share plans, because all of
a suddent there may be a table Joe.tbl.

This is a little different on SQL 2005 where users can have a default
schema which does not agree with their username, for instance "dbo".

Within stored procedures, it should not have any difference as far
as I can see, but I have heard people from Microsoft say that it has.

For the database, relies on the current database, unless you are running
cross-database queries. Including the database name, makes life difficult
when you want to run against a different database.


> If someone change order of sql code blocks in my script - this may cause
> lose of it's context (like: use master / use <mydb>..). I wish to have my
> sript independed on changes like this and always produce correct result.

What I said above applies to application code. For an installation
script it may be different. Then again, if you want to run it in
several databases, you are going to hate yourself for you put the
database name in.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]