Schemas in SQL Server

Schemas are nothing but logical grouping of objects either it may be a table or view or stored procedure or function or any other DB objects.


Schemas are nothing but logical grouping of objects either it may be a table or view or stored procedure or function or any other DB objects.
 
For example,

Am having an enterprise wide database where you will be having HR related tables and Employee related tables. In this case, employees won't have access to the HR related tables. In that case, we can group the tables under one roof like employee tables can be placed in Employee schema and HR related tables can be placed under HR schema.
 
Right click on the Your Database ->  Security -> Schemas -> New Schema

1.gif 
 
You will get the new schema option, provide the schema name and the schema owner under General tab.

2.gif

Under permissions tab, you can select the users who will come under this schema and also you can provide the type of access for each user under this schema.

3.gif
  
You can create your objects under the schema using the below query. (SchemaName.ObjectName)
 
CREATE TABLE venkatSchema.venkat(ID INT)
 
Accessing the objects can be done as Schemaname.ObjectName
 
SELECT * FROM venkatSchema.venkat