SQL Server Schemas

Posted a reply to:I am not a Schema

I've started adopting schemas, and have migrated a system that originally was designed with multiple databases to serve a single end data warehouse into schemas for two reasons:

1. DRI across schemas - before schemas, i had the ugly option of a trigger, or would even sync multiple copies of the same data between databases within the same server to provide a base in each db to DRI against. If a deletion or update in one, or in a master set, conflicted, it would be discovered at sync time rather than at the time of the change itself. Generally, in an OLTP application avoided the architecture that requires DRI to cross schemas. In the OLAP-oriented solution, it has worked well to have a single schema containing measure metadata, to provide validation for user entries in another schema, and provide a base for star schema tables in another schema.

2. Database objects and scripts can be written polymorphically - in a multi-tenant architecture, multiple databases required any views, sprocs, user-defined functions, or ad hoc scripts to reference the databases they touched (i.e. "SELECT * FROM {Customer}SourceData.dbo.RawData" with the {Customer} parameter having to precisely and manually be filled in...each... and...every... time a script was ran... and carefully too, to avoid accidental cross-polinations. With schemas, the SQL becomes "SELECT * FROM SourceData.RawData", and can be deployed across customer databases with no changes, and no risk of accidental cross-polination of data.