Schema In SQL Server

Schema In SQL Server

What is schema?

Hi friends, in this post we will learn about Schema in SQL Server. A schema is a container that holds tables, views, procedures, and so on. It is inside a database. The server is the outermost box, and the schema is the innermost box.

In earlier post we had discussed about SQL Server Operators, and Data Type in SQL Server.

User-Schema Separation

Schemas are no longer equivalent to database users , schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

  1. Ownership of schemas and schema-scoped securables is transferable.
  2. Objects can be moved between schemas.
  3. A single schema can contain objects owned by multiple database users.
  4. Multiple database users can share a single default schema.
  5. Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.
  6. A schema can be owned by any database principal. This includes roles and application roles.
  7. A database user can be dropped without dropping objects in a corresponding schema.Default schema for a database is Dbo by default.

Create schema Query:-

create schema test
create table test.table1 (id int,name varchar(20))

----------------------Now Select Like this-------------------------------

select * from test.table1