Model Database

Model database pay a very important role in SQL Server. In this article I am trying to discuss something related to it. Hope all of you like that.

Model database used as templates of all database created in SQL server. The entire contents of the model database are copied to the new database. When the CREATE DATABSE statement is issued the first part of the database is created by copying the model database and rest of the new database is filled by empty pages. If we make any modification on the model database all the other database created after, copied all the changes.

If you would like to have certain objects in each user database you can add such objects to the model. For example, you might wish to permit a particular set of logins to have access to all user databases. If so, you can permit those logins to access the model database. Each time you create a new database the same logins will also be granted access to the new database. 

The Model database does not contain any system table’s specific to it

Physical properties of Model database are mentioned bellow:  

File	Logical Name	Physical Name	File growth
Primary data	modeldev	model.mdf	Auto grow 10%
Log	modellog	Model.ldf	Auto grow 10% up to 2 TB

The bellow table describe the default value of the database options and can be modified.

Database option	Default value	Can be modified
ALLOW_SNAPSHOT_ISOLATION	OFF	Yes
ANSI_NULL_DEFAULT	OFF	Yes
ANSI_NULLS	OFF	Yes
ANSI_PADDING	OFF	Yes
ANSI_WARNINGS	OFF	Yes
ARITHABORT	OFF	Yes
AUTO_CLOSE	OFF	Yes
AUTO_CREATE_STATISTICS	ON	Yes
AUTO_SHRINK	OFF	Yes
AUTO_UPDATE_STATISTICS	ON	Yes
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Yes
CHANGE_TRACKING	OFF	No
CONCAT_NULL_YIELDS_NULL	OFF	Yes
CURSOR_CLOSE_ON_COMMIT	OFF	Yes
CURSOR_DEFAULT	GLOBAL	Yes
Database Availability Options	ONLINE MULTI_USER READ_WRITE	No Yes Yes
DATE_CORRELATION_OPTIMIZATION	OFF	Yes
DB_CHAINING	OFF	No
ENCRYPTION	OFF	No
NUMERIC_ROUNDABORT	OFF	Yes
PAGE_VERIFY	CHECKSUM	Yes
PARAMETERIZATION	SIMPLE	Yes
QUOTED_IDENTIFIER	OFF	Yes
READ_COMMITTED_SNAPSHOT	OFF	Yes
RECOVERY	Depends on SQL Server edition1	Yes
RECURSIVE_TRIGGERS	OFF	Yes
Service Broker Options	DISABLE_BROKER	No
TRUSTWORTHY	OFF	No

Posted by: MR. JOYDEEP DAS