Sub-Schemas in SQL Server 2005/2008

Question

This is a simple question yet I was unable to find any information at all about this.

Is it possible to have sub-schemas in SQL Server 2005/2008?

Example: Having a HR (Human Resources) schema with a sub-schema called Training (with tables related to this). It would end up like HR.Training.* where * would be the tables.

Answer 1

No. You could fake this with roles by putting different users into different roles and allowing those roles to use objects.

Answer 2

Maybe you could fake it in the naming of the schema, like HR_Training.* and HR_Reviews.* and so forth. Cheesy, I know.

Answer 3

Are you coming from an Oracle background by any chance ? Oracle has the concept of Schemas I believe. In SQL Server the closest equivalent is a Database.

You can cross-query from one database to another on the same SQL server very easily and that would give you virtually the same kind of calling syntax

e.g server.database.owner.object

In you case it might look like HRSvr.HR.dbo.xxx and HRSvr.Training.dbo.xxxx.

Answer 4

Can you please say in normal cases dbo stands for what? owner or database?