CREATE DATABASE
No, not the fact that anyone can create a database but the "advanced" options.
The amount of times I have seen this statement butchered is enough to warrant a post.
Do you have a database this is over 250Mb in size that has 1 data file?
My "best practice" for this is that only system tables (and associated objects.. SP, views, triggers etc..) should reside on the PRIMARY file group.
You should, as a minimum have 2 filegroups, and in most cases three. Forget the log filegroup for the moment as I am only concerned with data.
As a in house standard, we have 3.. PRIMARY, DATA and INDEXES
After creating the database use the ALTER DATABASE command to make the DATA filegroup the DEFAULT (that is, all tables not explicitly stating their Filegroup will be placed here.)
Clustered indexes should also go in the DATA filegroup and all non-clustered indexes on the INDEXES File Group
These options should be included from the development tier and up, as having to go through a database script and altering them as you move through environments sucks pretty bad and is error prone.
The PRIMARY filegroup could then be restricted in size (50Mb is a hell of a lot of meta data and would be a very big schema.).
FielGroups can be marked as readonly, backedup separately and most importantly, can take advantage of multiple IO controllers in you storage system.
Another advantage is that you can target device size for your backups... Say you back up to CD (I'm just giving an example), you could then create multiple files within the filegroup that match the CD size (~700MB).
Not really a common scenario, but without multiple filegroups and files, you don't have a chance in hell..
A gotcha from this approach is that if you have any base tables that are in your model DB.
You cannot add Filegroups to this database and thus, any tables will placed in the Primary File Group.
I recommend that you create a template in QA and use this every time you need to create a database...
Print | posted on Tuesday, February 24, 2004 10:08 AM