Tuesday 27 March 2012

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

1 comment:

  1. It is very good blog and useful for students and developer , Thanks for sharing
    .Net Online Course Hyderabad

    ReplyDelete