Are you taking advantage of the Model Database installed with every instance of SQL Server? As you may know, when you create a new database on an instance of SQL Server, it first makes a copy of the model database schema and data. This means your new database will clone any of the following:
- User Tables
- User Views
- Stored Procedures
- Scalar and Table Functions
- User Defined Types and Table Types
If you leverage this capability it can save you a lot of time whenever you create a new Database. I like to include a Tally table in all of my databases, at table with a sequence of number, usually from 0 to 100,000. This is an extremely useful table, and simplifies many different kinds of queries. Because of the usefulness, I add it to my Sample database on every instance of SQL Server. Then I don’t have to think about it when I create a new database.
Perhaps you notice I have said you have to maintain the Sample database on every instance of SQL Server. That is because each instance of SQL Server has its own copy of a Sample database. If you manage your own SQL Server installations, you can modify the install scripts that create the sample database, thus assuring that each instance of SQL Server, installed from your version, has the same implementation of the Sample database.
If you really take on the practice of leveraging the Sample database, then it becomes important to maintain changes in version control and change control, just as you do any other database. Migrating modifications to you different SQL Server instances becomes an interesting task. If you maintain changes to your Model database, you’ll find it much easier to support the standardization it provides.
Cheers,
Ben