Default schemas in SQL Server can be a blessing, since they reduce the need to specify the schema when creating DDL statements in T-SQL. However, relying on the default schema when creating DML statements can be problematic. A recent question
on dba.stackexchange.com asked “Does T-SQL have a Schema search path?”, similar to PostgreSQL implements the
parameter. This post shows how schemas
are implemented in SQL Server. We’ll also see why it’s important to always specify the schema when using SQL Server.
A lot of this behavior goes back to the pre-2005 era. 2005 introduced schemas as logical separators, whereas pre-2005 they were more of a security measure (and
was the database owner’s schema). I completely agree that you should specify two-part names in-database. It’s a tiny bit faster (which adds up when you’re doing thousands of transactions per second) and reduces ambiguity.