Schema in SQL Server

A schema in SQL Server is a container for organizing and grouping database objects such as tables, views, stored procedures, and functions. Schemas provide a way to logically group objects together and control access to the objects within a schema.

Each SQL Server database can have multiple schemas, and each schema can have multiple objects within it. By using schemas, you can improve the organization and management of your database objects, making it easier to understand the structure of your database and manage access to the objects within it.

Here's an example of how you might create a schema in SQL Server:

CREATE SCHEMA HumanResources

This creates a new schema named HumanResources. You can then create tables and other objects within this schema:

CREATE TABLE HumanResources.Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName NVARCHAR(50),

    LastName NVARCHAR(50),

    HireDate DATE

)

GO

Now, you have a table named Employees within the HumanResources schema. You can access this table using the fully qualified name HumanResources.Employees.

You can also assign permissions to schemas, allowing you to control who has access to the objects within the schema. For example:

GRANT SELECT ON SCHEMA::HumanResources TO UserA

GO

This grants the SELECT permission on the HumanResources schema to the user UserA, allowing them to query data from the Employees table.