SQL Interview: 113 model database

SQL Interview: 113 model database

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Intro

Question:

You are using Object Explorer in SQL Server Management Studio (SSMS) to browse the contents of a SQL Server. In the System Databases folder, there is a database named model. What is its purpose?

Answer:

The model database acts as a template database. Whenever SQL Server creates a new user database, that database is created as a copy of model.

This means:

  1. Default structure and objects for new databases

Any objects you place in model — tables, views, stored procedures, users, roles — will automatically appear in every new database you create.

For example, if you add a table called settings to model, every new database will contain that table. Or if you set a default collation or recovery model in model, new databases inherit those settings.

  1. Default configuration for tempdb

tempdb is recreated every time SQL Server starts, and its creation is also based on the model database. So, configuration changes to model can affect tempdb (though Microsoft recommends directly configuring tempdb instead of relying on model, for clarity).

2025-12-31