Schemas in Azure Synapse Dedicated SQL Pool | Create, Use & Manage with T-SQL | Azure Synapse Analytics Tutorial

Schemas in Azure Synapse Dedicated SQL Pool | Create, Use & Manage with T-SQL

📘 What is a Schema?

A schema in Azure Synapse Analytics is a namespace that logically groups database objects such as tables, views, and stored procedures. Schemas help improve manageability, security, and object organization in your Synapse Dedicated SQL Pool.

🔹 Benefits of Using Schemas

• Helps logically separate different business domains (e.g., Sales, HR)
• Supports security boundaries and role-based access
• Improves development organization and collaboration

🛠️ Step-by-Step: Create and Use Schemas

✅ 1. Create Schemas

CREATE SCHEMA SalesSchema;
CREATE SCHEMA HRSchema;
GO

✅ 2. Create Tables Under Schemas

CREATE TABLE SalesSchema.SalesOrder (
    OrderID INT,
    CustomerName NVARCHAR(100),
    Amount MONEY
)
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
);
GO

CREATE TABLE HRSchema.Employee (
    EmployeeID INT,
    FullName NVARCHAR(100),
    Department NVARCHAR(50)
)
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
);
GO

✅ 3. Insert Sample Data

INSERT INTO SalesSchema.SalesOrder (OrderID, CustomerName, Amount)
VALUES (1, 'Alice', 1200.00),
       (2, 'Bob', 1800.50);

INSERT INTO HRSchema.Employee (EmployeeID, FullName, Department)
VALUES (101, 'John Doe', 'Finance'),
       (102, 'Jane Smith', 'HR');
GO

✅ 4. Query the Tables

SELECT * FROM SalesSchema.SalesOrder;
SELECT * FROM HRSchema.Employee;

🔐 Security Use Case

You can assign different permissions to schemas, such as allowing HR team access to HRSchema but not SalesSchema:

GRANT SELECT ON SCHEMA::HRSchema TO HR_ReadOnly_Role;
DENY SELECT ON SCHEMA::SalesSchema TO HR_ReadOnly_Role;

📌 Best Practices

• Use separate schemas for different departments or modules
• Apply schema-level permissions to simplify security management
• Prefix schema names to clearly indicate purpose (e.g., SalesSchema.Orders)

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.