Laurenz Albe makes a comparison:
Recently, somebody asked me for a reference to a blog or other resource that describes how schemas work differently in Oracle. I didn’t have such a reference, so I’m writing this article. But rather than just describing Oracle schemas to a reader who knows PostgreSQL, I’ll try to present the topic in a way that helps Oracle users understand schemas in PostgreSQL as well. Since I already wrote about the difference between database transactions in Oracle and PostgreSQL, perhaps this can turn into a series!
Click through for the comparison. As far as SQL Server, here’s my off-the-cuff take:
- Users and schemas — SQL Server follows the same model PostgreSQL does.
- Schemas as namespaces — SQL Server follows roughly the same model Oracle does.
- Schemas and object ownership — Different from the two, based on my reading. Objects are owned by the security principal that owns the schema. This is closer to the Oracle model but isn’t quite the same.
- Schemas and privileges –Because of how object ownership works, there’s more flexibility to the SQL Server model, but also more complexity. In practice, it’s closer to the way PostgreSQL works.
- Default schema — Different from the two. With one-part naming, SQL Server will first try the user’s default schema. If the object is not there, it checks dbo. That check, by the way, takes a small amount of time but can add up if you’re talking hundreds of thousands of transactions per second. Just ensuring you have consistent two-part naming can be a marked performance improvement on extremely busy servers.
- System schemas — The sys schema includes system tables and Dynamic Management Views, and INFORMATION_SCHEMA provides the types of information the ANSI SQL standard requires.