SQL Server model database

 SQL Server model database

1.Create a new Database會套用model db的 Database Options

(1)可以調整修改的 Database Options

例如: AUTO_SHRINK, AUTO_UPDATE_STATISTICS, Database Availability Options ....等，詳細表列請見下方官文文件連結

(2)可以自訂義建立Table、View、Stored Procedure

2.tempdb重建時，會套用部分model db的設定

3.無法自訂義file group，也無法自訂義data file，也就是無法增加file group與data file

Restrictions

The following operations cannot be performed on the model database:

• Adding files or filegroups.
• Changing collation. The default collation is the server collation.
• Changing the database owner. model is owned by sa.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the primary filegroup to READ_ONLY.
• Creating procedures, views, or triggers using the WITH ENCRYPTION option. The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

model Database - SQL Server | Microsoft Learn