What is a schema in SQL Server, and how is it different from a database?
Quick Answer
A database is the container of all objects and its own storage and transaction log. A schema is a namespace within a database that groups objects and acts as a permission boundary — dbo is the default. You reference objects as schema.table (e.g. sales.Orders).
A database is the whole thing — its own files, its own transaction log, its own set of objects. A schema is a namespace inside a database, a way to group tables and procedures and to set permissions as a unit. The default schema is dbo, and you address objects as schema.table. In a big database I'd use schemas to separate modules — sales, hr, audit — and grant access per schema. And it's good practice to always write the two-part name in code rather than relying on the default.
- Use schemas to organise a large database by module (
sales,hr,audit) and to grant permissions at the schema level (GRANT SELECT ON SCHEMA::reporting TO ...). - A schema is owned by a user/role, decoupled from object ownership, which makes dropping users easier than in old SQL Server.
- Always use the two-part name (
dbo.Customers) in code — it avoids a name-resolution step and is clearer. - Cross-database references use three parts (
db.schema.table); cross-server, four parts.