• Repost - originally posted 21-09-2012

Schemas and filegroups

In our consulting travels we don't see a lot of databases that use schemas. Schemas provide a logical grouping, so associated tables can be grouped together. Examples of this may be an Authentication schema to group tables such as users and roles. Or customer tables being in a customer schema (e.g. Customer Details, Customer Orders, Customer Billing etc). This can lead to simpler naming conventions for your objects. I usually use a Report schema for all reporting stored procedures and any aggregated reporting tables. Once you have started using this logical grouping you can then extend the power of schemas by placing each schema onto its own file group. Then all the Authentication tables are in the Authentication file group on their own physical file. This gives you the ability to easily move groups of tables onto different IO subsystems to improve performance if a hot spot has been identified. Schemas can also help in security by limiting access to certain schemas and they can also provide the ability to easily decouple parts of your application.


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t