Why do experienced SQL Server DBA's qualify examples with the default schema dbo? [closed]
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 ); SELECT * FROM foo; Assuming a default SEARCH_PATH (which is public) the above can be written explicitly as,CREATE TABLE public.foo ( int a ); SELECT * 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 ); SELECT * 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 explicitly. 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
MS SQL Server uses a fixed search order for unqualified names. So if you do not want your queries to depend on 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 dynamically. 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. Hope this helps you solve sql server dbo.