I will soon be migrating to SQL Server 2017 and hence am working on beautifying some neglected aspects of our databases. One aspect is filegroups: my DBA predecessor has created multiple filegroups however as no one was really told about most objects just go to the default filegroup (PRIMARY).
Now I would like to enforce that people creating tables and indexes actually name the filegroup where their objects should reside. Meaning: A CREATE TABLE or CREATE INDEX without an "ON [Filegroup]" should roll back with an error that the filegroup is missing. Is there any way to do this an would you recommend proceeding with such an approach? I have just come across that policy based management does not work in preventing objects being created on the default filegroup.
Main point of doing this is that someboy should choose the filegroup for the objects in the first place instead of creating them in the default filegroup and afterwards have to move the objects based on rants by the DBA :-).
I thought about two techniques...however can't get started with either of them because of missing knowledge:
- Use database triggers to check if there is an on clause and if not roll back --> this seems to be quite fragile as it would depend on text analysis of the command text
- Make the primary filegroup really small in size (100KB?) in order for system tables still being able to fit in but other tables of moderate size would result in an error because there is not enough space left --> probably a bad idea as this could have severe side-effects on database availability and is not precise enough in preventing every user defined table or index to be created within the default filegroup
Thank you very much in advance for your help