[dba-SQLServer]Qualifying Object Names

Djabarov, Robert Robert.Djabarov at usaa.com
Mon May 12 10:19:35 CDT 2003


Actually, the main reason behind qualifying object names with schema owner is to eliminate the need for the optimizer to look for the same object under the schema name of the logged on user.  Following MS best practices, all objects need to be created by SA.  If that's the case, - the fact that others are going to use the objects does not require full schema qualification because after looking for the object of the schema owner of the logged on user the optimizer will look for the object created by the database owner.  However, it is a good practice to always fully qualify object names with schema owner.

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
? (210) 913-3148 - phone
? (210) 753-3148 - pager


 -----Original Message-----
From: 	Arthur Fuller [mailto:artful at rogers.com] 
Sent:	Monday, May 12, 2003 7:16 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	RE: [dba-SQLServer]Qualifying Object Names

The main reason you should qualify names is because other users will be
accessing your stuff. Suppose you log on as 'sa' and then create some stuff.
As sa, you can SELECT * FROM myTables and it will work. Should any other
user issue the same command, it won't work. I got bitten by this problem in
my first ADP project and learned my lesson quickly. So all record and row
sources in my ADPs are now qualified.

It is not necessary to qualify every single column name etc., however. A
single reference will do, like so:

SELECT Customers.CompanyName, Orders.OrderID 
FROM dbo.Customers INNER JOIN dbo.Orders ON Orders.CustomerID =
Customers.CustomerID

You should qualify sprocs, views, UDFs and tables. I do it out of habit now,
even if I'm the only user of the given objects.

A.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: May 11, 2003 9:43 PM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer]Qualifying Object Names


Just a general question.  Some books suggest that when writing code you 
should always qualify objects with the owner name (eg dbo.tblCustomers).

How far should this be taken?  Should I be doing this for every field in 
every sproc? or are there sets of guidlines?  What benefits are there of 
including the owner name?

Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list