The dbo. prefix in database object names, can I ignore it?

Question

I am looking for a performant default policy for dealing with the .dbo prefix.

I realize that the dbo. prefix is more than syntactic noise, however I got through the past 8 years of MS based development skipping typing the dbo. prefix and ignoring its function.

Apart from a performance issue with stored proc compile locks is there a downside to skipping typing ".dbo" in SQLqueries and stored procedures?

Further background: All my development is web middle-tier based with integrated security based on a middle tier service account.

Answer 1

Most of the time you can ignore it. Sometimes you will have to type it. Sometimes when you have to type it you can just type an extra '.':

SELECT * FROM [LinkedServer].[Database]..[Table]

You'll need to start watching for it if you start using extra schemas a lot more, where you might have two schemas in the same database that both have tables with the same name.

Answer 2

"however I got through the past 8 years of MS based development skipping typing the dbo. prexfix and ignoring its function."

This is your answer. If your DB is performing fine you are OK. Best practices don't replace real testing on your actual system. If your performance is fine and maintenance is OK, your time is better spent elsewhere where you can get better bang for your proverbial buck.

Answer 3

After working in the oracle world, I would advise against skipping the schema declaration. Remember now that SQL server versions after 7.0 support multiple schemas per database - it is important to distinguish between them to be sure that you are grabbing the proper tables.

If you can ensure that you'll never have two separate schema namespaces per database, then you can ignore it. The dbo. prefix should do nothing to affect performance by itself - parsing is such a small part of the SQL query as to be insignificant.

Answer 4

The main issue is not security, is name conflict resolution, in the case that your application will ever be deployed side-by-side with another application using the same names in the database.

If you package and sale your product, I would strongly advise to use schemas, for the sake of your costumers. If you develop for a one particular shoppe, then is not so much of a concern.

Answer 5

[dbo].[xxx]

The SQL Server engine always parse the query into pieces, if you don't use the prefix definitely it going search for object in similar name with different users before it uses [dbo]. I would suggest you follow the prefix mechanism not only to satisfy the best practices, also to avoid performance glitches and make the code scalable.

I don't know I answered your question, but these are just my knowledge share

Answer 6

Yes you can ignore - for the most part - if you never ever create anything outside the (default) "dbo" schema. One place you can't ignore it is when calling a stored function - that always has to have the "two-part" notation:

select * from dbo.myFunc

However, it is considered a best practise to always use the "dbo." prefix (or other schema prefixes, if your database has several schemas).

Marc