After you create a database, you can also set its properties to change some of the settings at the time of creation and properties that cannot be set at creation time. The Database Properties Settings dialog box, shown in Figure 6-4, appears when you right-click the database where you want to set the properties and select option Properties from the shortcut menu.
In the General page box, you can see property information such as the status of the database, owner, creation time, capacity, backup, maintenance, and so on.
In the "Data Files" and "Transaction Log" page boxes, you can see a dialog box similar to Figure 6-2 and figure 6-3, which allows you to 诖 lip ㄊ a gourmet eggplant 匦 round to pay ㄊ the naan of the bud to raise to take ⒋ beer wa? Properties such as location, initial capacity size, and so on.
In the Filegroups page box, as shown in Figure 6-5, you can add or remove filegroups, but if there are files in the filegroup that cannot be deleted, you must first move the files out of the filegroup.
In the Options page box, you can set many of the database's properties, as shown in Figure 6-6. The option selected in Figure 6-6 is the system defaults to TRUE.
Each option is selected with the following meanings:
- Restrict Access
Access control.
- Member of Db_owner;dbcreator,or sysadmin
Only the database owner, the database creator, and the system administrator have permission to use the database.
- Single User
The database can only be used by one user at a time.
- Read only
The database is read-only and can be viewed without modification.
- ANSI Null Default
Allows you to enter a null value in the column of the database.
- Recursive triggers
Allows triggers to be invoked recursively. SQL Server triggers a recursive invocation of a trigger with a maximum number of 32 layers.
- Select into/bulk Copy
Allows a large number of data to be inserted into a table using the SELECT INTO or bcp, WRITETEXT, and UPDATETEXT commands. Inserting data with the above command does not have records in the transaction log. This option is often set to true when developing the database.
- Truncate Log On Checkpoint
When the checkpoint command is executed, its contents are purged if the transaction log file exceeds 70% of its size. This option is often set to true when developing the database.
- Torn page detection
The corrupted page (torn page) is automatically detected. Pages (page) are the basic storage unit for the contents of a database. The size of each page is 8KB. Because SQL Server reads and writes to a page as a 512-byte sector (sector), SQL Server considers the page to have been successfully written after the first sector of each page is successfully written. As a result, if an unexpected event occurs after the first sector is written, a corrupted page is generated in the write. The database needs to be recovered through backup.
- Auto Close
When there is no user in the database, the database is automatically closed and the resources that are occupied are returned to the operating system. Do not use this option for those databases that are in uninterrupted use, otherwise the cost of running the switch database will be increased.
- Auto Shrink
Periodically, the database is checked, and when the unused space of the database file or log file exceeds 25% of its size, the system automatically shrinks the file so that its unused space equals 25%. The file does not shrink when the file size does not exceed its initial size when it was established. The reduced file must also be greater than or equal to its initial size. The reduction of the transaction log file can only be done if it is backed up or when the Truncate log on Checkpoint option is set to true.
- Auto CREATE statistics
Starts using statistics for database columns to be available to the query optimizer (Optimizer).
- Auto UPDATE STATISTICS
When the database table is updated, all statistics are updated.
- Use quoted identifiers
Identifiers must be expanded in double quotes and may not conform to the Transact-SQL naming standard.
- Compatibility level
Database compatibility level. You can specify which version of the database is compatible with earlier.
- Other database options
There are also some database options that are not displayed, such as ANSI warning, merge publish, offline, published, subscribed, and so on, which are generally best controlled by SQL Server itself, When you really need to change it, you can do it by sp_dboption the system stored procedures. System stored procedure sp_dboption is used to display or change database options. However, it cannot be used in the system database master or the tempdb database. The syntax is as follows:
sp_dboption [[@dbname =] ' database_name ']
[, [@optname =] ' option_name ']
[, [@optvalue =] ' value ']
The parameters are described as follows:
- [@dbname =] ' Database '
Specifies the name of the database to be manipulated.
- [@optname =] ' Option_name '
Specifies the database options to display or modify. You can not enter the full name of the option. If this is omitted, all options that are set to True are listed.
- [@optvalue =] ' Value '
The value of the value ' value ' for the specified option can be true, False, or on, off.
Example 6-2: Displays the option state of the MyTest database.
exec sp_dboption ' mytest 'The results of the operation are as follows
The following options are set:
-----------------------------------
Select Into/bulkcopy
Trunc. Log on chkpt.
AutoShrink
Auto CREATE statistics
Auto UPDATE STATISTICS
Example 6-3: Set the MyTest database to a offline state of true so that the database is not available to normal users.
exec sp_dboption ' mytest ', ' Offline ', ' true '
The results of the operation are as follows:
sp_dboption command succeeded.
Note: The system administrator can set data options in the template database model by executing the sp_dboptin command, which changes the initial option settings for all new databases.
You can set the user's permissions on this database in the Permissions page box, as shown in Figure 6-7. For a detailed description of permission settings, see the "Security and User Management" section.
