In SQL, a schema is a logical collection of database objects such as tables, views, stored procedures, indexes, triggers, and functions. A schema is owned by a database user, who is known as the schema owner. A schema is a useful mechanism to segregate database objects for different applications, access rights, and managing the security administration of databases. A database can have one or multiple schemas, but a schema can belong to only one database. There are no restrictions on the number of objects in a schema. SQL Server provides us with a few built-in schemas such as dbo, guest, sys, etc. . A schema can be assigned security permissions, making it an effective method for distinguishing and defending database objects based on user access privileges. Some advantages of using a schema in SQL include:
- Allowing several users per schema
- Using the same schema with different databases
- Increasing the databases stability for security-related management
To create a schema in SQL, the CREATE SCHEMA statement is used. The syntax for creating a schema is as follows:
CREATE SCHEMA schemaname [AUTHORIZATION ownername]
Where schemaname is the name of the schema to be created, and ownername is the name of the user who will own the schema.
