When applications hardcode a schema name, dbo.TABLE, it reduces the DBA's ability to migrate the data as needed.

Is it safe and more flexible for applications to omit schema names?

I know you will say this is "opinion based," but it is a critical issue to managing databases.

  • 1
    When applications hardcode a schema name, "dbo.TABLE", it reduces the DBA's ability to migrate the data as needed.... uh, false. You can always place views on top of your tables with different schema names, so I think your question is based on a fallacy, which kind of invalidates the question in general. I don't think the question warrants a down-vote, but your thoughts on it being closed is likely accurate. – John Eisbrener Jun 24 '19 at 14:35
  • @JohnEisbrener - I am open to reasons to use schema names. If the DBA can always replace tables with views resolving to other schemas, then why have applications specify dbo.TABLE rather than just TABLE. If the dba creates a view, TABLE would resolve to dbo.TABLE which is a view to mrpv3.TABLE. What is the value of having source code specify dbo.? – lit Jun 24 '19 at 14:43
  • 2
    @lit - Please read Bad habits to kick : avoiding the schema prefix. – Scott Hodgin Jun 24 '19 at 14:45
  • @ScottHodgin - Yes, I read that before posting. And I agree, that in a poorly managed environment, multiple schemas can cause problems, But, specifying them puts more limits on how a DBA can manage the database. It removes the option of changing the account default schema and requires the use of views. Are there other mechanisms to manage this? – lit Jun 24 '19 at 14:54
  • I wrote a blog post explaining why you should always specify the schema over at sqlserverscience.com – Max Vernon Jun 24 '19 at 15:00

You appear to understand this, but just in case... you do not always have to specify the schema, as long as the schema is the user's default schema.

So you can handle this at the login level of SQL Server. A login can be set to default to, say, the schema 'MySchema'. For this login, the table mySchema.Orders would be the default for that user, addressable as simply Orders.

This isn't used often, since it can get confusing, but it is a fully functional way of doing this. But note: if you don't have the table mySchema.Orders in your database, the system WILL look for dbo.Orders as well. This is great if you want some tables personalized and some in common - bad if the mySchema users should not have access to dbo tables.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.