Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
redbrad0
Posting Yak Master
176 Posts |
 Posted - 2005-05-15 : 00:28:25
|
| Is there a benfit in telling the query the table owner when calling a query? I have been watching SQL Profiler with three types of query's....1. Select ID from database.owner.table2. Select ID from owner.table3. Select ID from tableUsing the #1 takes much longer but #2 & #3 are so close that I am not sure which one I should use in my SP's or Dynamic Query's built thru my ASP Pages. Does anyone have any insight on this?Quality NT Web Hosting & Design |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-05-15 : 07:42:07
|
From Books Online, "Database Object Owner":quote:
Referencing database objectsWhen users access an object created by another user, the object should be qualified with the name of the object owner; otherwise, Microsoft® SQL Server™ may not know which object to use because there could be many objects of the same name owned by different users. If an object is not qualified with the object owner when it is referenced (for example, my_table instead of owner.my_table), SQL Server looks for an object in the database in the following order: - Owned by the current user.
- Owned by dbo.
If the object is not found, an error is returned.For example, user John is a member of the db_owner fixed database role, but not the sysadmin fixed server role, and creates table T1. All users, except John, who want to access T1 must qualify T1 with the user name John. If T1 is not qualified with the user name John, SQL Server first looks for a table named T1 owned by the current user and then owned by dbo. If the current user and dbo do not own a table named T1, an error is returned. If the current user or dbo owns another table named T1, the other table named T1, rather than John.T1, is used.
You should also see a slight performance improvement by using the two-part name, since it reduces the number of lookups needed to resolve the name. |
 |
|
|
redbrad0
Posting Yak Master
176 Posts |
Posted - 2005-05-15 : 08:42:12
|
| Thank you very much. So from reading that if I have a table that is owned by my username then I should not specify it. If the table is owned by DBO or another user then I should specify the name. Is it standard to just always specify the name, or make sure all objects are owned by your username?Quality NT Web Hosting & Design |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-05-15 : 08:57:12
|
| The best practice is to make all objects owned by dbo, AND to always use the 2-part name. If you can't create all objects as dbo then you should definitely include the user name. |
|
|
|
redbrad0
Posting Yak Master
176 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-15 : 21:10:29
|
Of course, all this is changing in SQL Server 2005.  I just thought that would give you something to look forward to.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
redbrad0
Posting Yak Master
176 Posts |
Posted - 2005-05-15 : 22:11:48
|
| What will be the new changes in 2005 reguarding this? I have not even tested out 2005 but hear nothing but good things about it and cant wait for the release.Quality NT Web Hosting & Design |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-15 : 23:17:45
|
| Well, you will actually have real schema then. Right now, an owner is schema is a user. So, you usually just make dbo the owner of everything because anything else is awkward and breaks all the time. That doesn't make it right, but such is life. Since schemas actually work the schema.object naming is more practical to use, and should be used in many cases.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Specify Table Owner when calling a Query?