SQL Schema explained

What is a schema?

You can think of schemas as logical collections of database objects. If your famililar with coding in other languages, a schema is almost like a namespace.

The default namespace in SQL Server is dbo which you've probably seen in the form <schema>.<tableName> for example dbo.OrderHeader.

A schema can contain:

• Tables
• Functions
• Views
• Stored procedures
• and other database objects

Organize your database objects

In it's most basic form a schema can help you group and organize your objects. When developing custom code for applications like Manhattan SCALE WMS, I use some of the following schemas to separate my code from the main application code in schema dbo:

• custom - for custom tables, stored procedures, views and functions.
• exitpoint - for the application exit point code (common event trigger in Manhattan SCALE WMS)
• report - for procedures called by the reporting system

This way of separating my code from the dbo schema, makes it really easy to find and filter just the custom code.

The list of objects will be less cluttered, and I rarely need to view or alter the standard code in the dbo-schema. 

Enhanced security

Database schemas can also help you set access restrictions. When external applications like SSRS-reporting or Crystal reports need to access data I only want to allow these applications read access. A reporting service should never have acces to write to the production database.

Giving your report application write access is a security risk and really bad practice 😱

How do I use the objects?

Lets say you have created a report schema with a stored procedure for fetching sales orders sold today:

report.sales_orders_today

To execute this procedure I would run the following statement:

EXEC report.sales_orders_today;