Schema concept and Schema creation in SQL Server
Schema is a new concept introduced into SQL Server from version 2005, it is a namespace used to group together tables that share a certain feature for easy management. If you don't use schema in the database, the default schema will be dbo.
For example in your database schema there are two main types of tables as follows:
News tables -> I will create a schema named news that includes tables related to news
Product tables -> I will create a schema named products including tables related to products
In a database, the schema name is unique, conforming to the syntax: server.database.schema.object
Advantages of schema:
When you group the tables according to the prefix defined by the schema, it is very easy to manage, identify, or decentralize the management of each schema for different users, this is the main role of the schema.
Create Schema (Create Schema)
Create Schema with SSMS:
Create Schema with T-SQL
CREATE SCHEMA (<schema_name>)
CREATE SCHEMA (<news>)
Drop schema (Drop schema)
DROP SCHEMA (<schema_name>)
DROP SCHEMA news
Grant permission Schema
Assuming I want user toanngo92 to have management rights for schema news, just use the GRANT command, and delete the permission, use the DENY command.
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: news TO toanngo92
DENY SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: news TO toanngo92
So grouping like this helps your database schema look more professional, easier to decentralize and more secure.