In PostgreSQL like in SQL Server we have a default schema under a normal install. In PostgreSQL it's public.

CREATE TABLE foo ( int a );

FROM foo;

Assuming a default SEARCH_PATH (which is public) the above can be written explicitly as,

CREATE TABLE public.foo ( int a );

FROM public.foo;

In SQL Server, there seems to be a default schema of dbo (where all database objects are stored by default). This means you can write,

CREATE TABLE dbo.foo ( int a );

FROM dbo.foo;

Like in PostgreSQL, in Microsoft SQL Server you can change it. If you do, so long as you never qualify it things just work. In PostgreSQL the convention is to never write the default schema unless required. So in examples and in ETL-code you'll never see the public schema explicit. But it almost seems like the convention in Microsoft SQL is to always write the default schema (dbo). Why is that? It seems like this just closes the door to users who don't have write access to dbo, or who otherwise don't want to pollute the main schema. I'm going to assume there is a good reason for this convention in SQL Server. Why not always omit an explicit dbo.?

You can see

  • 1
    One reason is we dbas don’t like to assume naming conventions. By specifying dbo, original intent is always understood. Plus, sometimes we are just the executors of a dev team. So there’s that. – clifton_h May 13 '19 at 5:40
  • 1
    "If something is not specified directly, it can be random"... – Akina May 13 '19 at 5:41
  • I guessing to what "search_path"means (like path in OS), and if I'm right then the answer is no. Name resolution is simple in SQL Server. If you don't specify schema, check your default schema and if object isn't there, then check dbo schema. One reason (among other) for why we specify schema is that back in the days (SQL 2000), you paid more if you didn't specify schema, where that penalty would be recompiles in some situations. – Tibor Karaszi May 13 '19 at 6:49
  • This is an old article but it's written by one of the people you mention: Bad habits to kick : avoiding the schema prefix. – Andriy M May 13 '19 at 9:33
  • 2
    Evan, you know this is primarily opinion based... – John Eisbrener May 13 '19 at 13:07

MS SQL Server uses a fixed search order for unqualified names (the user's default schema, and the dbo schema). So if you do not want your queries to depend on that what the user's default schema happens to be, you always have to use schema-qualified names.

In PostgreSQL, the schema search order can be modified dynmically. The documentation says:

Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in.

In other words, the Postgres schema lookup mechanism was specifically designed to allow omitting schema names in most queries.

(The default Postgres search path is "$user", public, which is essentially the same as that of MS SQL Server, and can lead to the same problems. But in practice, the default databases privileges do not allow users to actually create their own schemas.)

| improve this answer | |
  • 1
    In addition, there are scenarios where you must specify the schema (in particular, when creating objects qualified WITH SCHEMABINDING). Getting into the habit of always specifying the schema can be the result of working in such scenarios often. – Andriy M May 13 '19 at 9:27

Answering from the MS SQL perspective:

You can have objects with the same name in different schemas.

So you can have a table dbo.orders and german.orders and french.orders and italian.orders.

To make it clear from the very beginning, which of the objects you mean, it is best practice to always specify the schema (there are a few statements that won't work, if you skip it).

There is also a (leagacy?) reason to NOT specify the schema and just SELECT * FROM orders - you can assign a default schema to a user. So you could assign German users to the [german] schema, the French to [french] etc. and can use the same code working for all users but accessing different tables (which could be placed in different file groups / files). This allows you to realize a strict client based approach in a single database, where everybody can only see / access the data from his own client (plus some global configuration tables as maybe global.products, where you always have to specify the schema).

To be honest, I have never seen this in the real life, but it is possible (and I was told, that it was more common in the early 1990th). Today I would usually prefer to use different databases or row based permissions (although those have a performance penality)

| improve this answer | |

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