-1

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
  • @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
  • If you understand that its not a good practice, an alternative will be to use SYNONYM – Kin Shah Jun 24 '19 at 17:30
  • @MaxVernon - I read the post. It does clearly explain how SQL Server works. My opinion is still that hardcoding a schema name into source makes the code less flexible. Without a schema name, the application can run without any changes by the DBA setting the default schema for the execution user. And, it is one less thing where error can be introduced. – lit Jun 24 '19 at 19:50
0

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 | |
4

Short answer here is No, it is neither "safe", nor "expedient", and arguably not more flexible to omit the schema name. In fact, doing so is one of the classic ways of "shooting yourself in the foot."

One of the primary rules of programming is to code in a defensive style. This is a well known tactic that helps prevent bugs, especially the hard-to-track down kind. Defensive programming is defined as:

Designing with foresight to ensure a piece of software continues functioning correctly under unforeseen circumstances.

Of note here is that every single object in SQL Server belongs to a specific schema. Most often, that schema is simply the dbo schema, and one can quite easily design an entire database full of stored procedures, views, functions, etc and never even realize there is a concept of schemas, since everything will just, by default, be created in the dbo schema. One could do this, however I would not recommend it, for the following reasons:

  1. It makes for hard to interpret code. Looking at the two SELECT statements below, there is more clarity around the one where the schema is explicitly specified.

    SELECT name FROM databases;
    
    SELECT name FROM sys.databases;
    

    Since I've specified sys.databases, you know instantly, and without question that results will come from the system view. Specifying the schema makes complex code far easier to reliably understand.

  2. Schema resolution may not always work as you intend. If you have multiple schemas in the database, SQL Server needs to perform a search for objects when you don't specify the schema. When you specify the schema, SQL Server knows exactly where to look for the object.

    • Two developers, Melanie and Hannah, are working on code. Melanie has their default schema set to 'Melanie', Hannah has their default schema set to 'Hannah'. Melanie creates the table, x, inadvertently in the Melanie schema since they didn't specify dbo in the CREATE TABLE statement. Melanie tells Hannah to start using table x. Hannah then proceeds to use table x without specifying a schema, but since both developers have different default schemas assigned to their login, their new code cannot locate the object. This looks like a bug to Hannah, meanwhile when Melanie runs the same code, it works perfectly. If both developers specify the schema, they will never see this bug.

    • Your database has two schemas, warehouse and accounting. Both schemas have a table named Invoices. The warehouse.Invoice table contains details about invoices that need to be processed. The accounting.Invoices table contains all the columns pertinent to the actual invoice, such as billing details, amount payable, etc. Code written by a user who has neither warehouse nor accounting as their default schema, who doesn't specify the schema name, will return an object resolution error either at run-time or when the code is being saved in to the database. In the code below, I'm creating the two invoices tables, in separate schemas:

      CREATE SCHEMA warehouse
      CREATE TABLE invoices
      (
          i int NOT NULL
              PRIMARY KEY CLUSTERED
          , HasShipped bit NOT NULL
              DEFAULT (0)
      );
      GO
      CREATE SCHEMA accounting
      CREATE TABLE invoices
      (
          i int NOT NULL
              PRIMARY KEY CLUSTERED
          , TotalAmount decimal(10,4) NOT NULL
      );
      GO
      

      Now, if I attempt to create a view without specifying schemas, I get an error:

      CREATE VIEW SomeView
      AS
      SELECT *
      FROM invoices;
      GO
      
      Msg 208, Level 16, State 1, Procedure SomeView, Line 4 [Batch Start Line 25]   
        Invalid object name 'invoices'.

      Now, take the example of creating a stored procedure in the dbo schema:

      CREATE PROCEDURE myproc
      AS
      BEGIN
          SELECT *
          FROM invoices;
      END
      GO
      

      When you execute the code to create that procedure, SQL Server reports no errors, due to SQL Server's Deferred Name Resolution. However, when you go to execute the proc, you get the following error:

      Msg 208, Level 16, State 1, Procedure myproc, Line 4 [Batch Start Line 42]  
        Invalid object name 'invoices'.
  3. Performance can suffer. In a variety of ways.

    • Execution Plans that would typically be shared among users with different default schemas will be cached separately if the T-SQL statement referenced in the Execution Plan doesn't have schema prefixes. So, for example, the following simple T-SQL statement would be cached multiple times for each user that has a different default schema:

      SELECT UserName FROM Users;
      

      For a complex database, with many varying T-SQL statements being executed, by many different users, this can result in significant plan-cache bloat, which can negatively impact query execution. Not to mention each user may get a significantly different plan depending on the complexity of the statements being executed, and whether parameter sniffing is in play.

    • In an environment where there are many schemas and many objects, specifying the schema name limits the work SQL Server must do when resolving object names to object IDs at compilation time. Specifying the schema can add up to a significant reduction of load for large, busy databases, with complex code.

    • Spinlock contention can become problematic when you don't specify the schema. This Microsoft Document on Diagnosing and Resolving Spinlock Contention states:

    Fully qualifying names of all objects will result in removing the need for SQL Server to execute code paths that are required to resolve names. We have observed contention points also on the SOS_CACHESTORE spinlock type encountered when not utilizing fully qualified names in calls to stored procedures. Failure to fully qualify these names results in the need for SQL Server to look up the default schema for the user, which results in a longer code path required to execute the SQL.

  4. Some objects do not have a default schema. Take the example shown here by Maria Zakourdaev regarding DDL triggers. When you create a server-side trigger, that trigger has no concept of a default schema, and so will fail at run time, which can be particularly painful. Maria admits in the article that they don't usually specify schemas, unless the code demands it. I would submit that if they were in the habit of always specifying the schema, Maria would have not spent hours chasing down that bug.

In summary, I always specify the schema for every bit of code I write, simply because it takes almost no effort while writing the code, and it guarantees me certain bugs simply will never happen.

Specifying the schema is low cost and high impact, which I think we can all agree is A Good Thing™.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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