Which 1 do u recommend and why?

Question

I think performance wise 2nd is faster Which 1 do you recommend and why? SELECT * FROM dbo.customers SELECT * FROM customers

Prabhu Raja · Answer

Hi I think 'dbo' will not speed up query Execution. The Performance depends on several issues like for example if the table has clustered indexes or not. Indexes are main factor used in Select Query execution by sql server. i suggest you to refer this Article for optimizing query performance. http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/

Vulpes · Answer

The article which Dipal found refers specifically to stored procedures. Here's one which refers to dynamic name resolution generally the conclusion being the same: http://blogs.msdn.com/b/mssqlisv/archive/2007/03/23/upgrading-to-sql-server-2005-and-default-schema-setting.aspx

Dipal Choksi · Answer

the prefix is referred to as owner qualification. it is recommended to include the owner qualification. Here is some information on a study on the performance difference when owner qualification is not included http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx

Suthish Nair · Answer

dbo is the default schema every database have. It will be always a good practice to have table name with dbo or the schema where table resides..

Vulpes · Answer

Oops Javeed's right - 'dbo' is the database owner; I think I read it as just 'db' to start with. However I'd still expect any performance difference between the two to be negligible.

Javeed M Shaikh · Answer

Hi Amol I think dbo is the database owner and not the database name every database is default owned by dbo so both the query would gave same performance if not it will be very very difficult to find any visible difference.

Vulpes · Answer

I'd have thought that the first option would be slightly faster because the query analyzer doesn't have to figure out in which database the customers table is located. However the difference will be miniscule and I'd only recommend actually using the first option if it were needed to resolve references to two (or more) tables with the same names in different databases.