CODEWITHSUNDEEP
sqlsql-server
camelCase
camelCase

Reputation:

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

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.

Upvotes: 15

Views: 23742

Answers (6)

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

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.

Upvotes: 9

marc_s
marc_s

Reputation: 754518

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

Upvotes: 3

Tamil.SQL
Tamil.SQL

Reputation: 262

[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

Upvotes: 12

Russell Steen
Russell Steen

Reputation: 6612

"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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294287

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.

Upvotes: 3

Chris K
Chris K

Reputation: 12351

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.

Upvotes: 1

Related Questions