Schemas in SQL Server

Schemas in SQL Server

I often see people are confused with term Schemas in sql some used to say schema is a group of Database tables and table Object but in actual it's quite wrong.

A Schema is a container of object which includes table,stored procedures,view ,function.etc

Think in a way that a Database have n no. of tables and you have divided the table in diff schema like All  Department type table,store proc into Department Schema and Sales type into Sales Schema Employee in Emp schme

Below is the Diagram which give you a clear Understanding on Schemas.

Now you can see in the above diagram you have a database inside that you have 3 schemas the Deafault schmea is DBO and other two schema are EMP and Sales  you can have n no. of tables in a schema so that it will differentiate the tables and table objects based on schema Names like all sales type table you can put inside table schema Employee type in EMP Schema rest in Default Schema  .

Syntax of Creating Schema

--

create schema sales

--syntax of creating table inside Schema

create table sales.orders (orderid int ,orderprice float)

IF you want to see data present in orders tables

You have to  write Schemaname before table name

example

Select * from sales.orders

You can Set Permission on Schema Levels  like granting Select Permissions to Users.

NOTE : The Default schema of Sql is dbo if you will create a table with mentioning schema name by default it will create inside dbo and there is no need to write schema name before table name while fetching records when your table is inside your default schema.