Schema Usage

Recently, I have learned the usage of SYNONYM and through it, I realized that SCHEMA can also be used to better leverage alias.

I have never really fully understood the usage of SCHEMA as I have been googling/researching this topic in the past days. I understand that we can use SCHEMA to break up a group of tables by department for example:

Server.Database.HRDepartment.Table1
Server.Database.ITDepartment.Table1
Server.Database.FinanceDepartment.Table1
Server.Database.MarketingDepartment.Table1

But what is the benefit of it vs something like this?

Server.Database.dbo.HRDepartment_Table1
Server.Database.dbo.ITDepartment_Table1
Server.Database.dbo.FinanceDepartment_Table1
Server.Database.dbo.MarketingDepartment_Table1

The one benefit that I understood from my research is that you can apply security on a SCHEMA.

I guess what I am seeking on this question is what are the real benefits of using SCHEMA? What would be some of real world usages of it, similar to the department example that I have provided above.

Thank You

As per your naming of tables, what if you have more than one table in any of the departments? you need to apply security individually on every table. but when you use schemas,you just apply the security on the schema and all the table inherit the security automatically.

1 Like

Hi Ahmeds, so is the usage of Schema mainly for security?

Yes,that is the main purpose.

1 Like