Searching the Help
To search for information in the Help, type a word or phrase in the Search box. When you enter a group of words, OR is inferred. You can use Boolean operators to refine your search.
Results returned are case insensitive. However, results ranking takes case into account and assigns higher scores to case matches. Therefore, a search for "cats" followed by a search for "Cats" would return the same number of Help topics, but the order in which the topics are listed would be different.
Search for | Example | Results |
---|---|---|
A single word | cat
|
Topics that contain the word "cat". You will also find its grammatical variations, such as "cats". |
A phrase. You can specify that the search results contain a specific phrase. |
"cat food" (quotation marks) |
Topics that contain the literal phrase "cat food" and all its grammatical variations. Without the quotation marks, the query is equivalent to specifying an OR operator, which finds topics with one of the individual words instead of the phrase. |
Search for | Operator | Example |
---|---|---|
Two or more words in the same topic |
|
|
Either word in a topic |
|
|
Topics that do not contain a specific word or phrase |
|
|
Topics that contain one string and do not contain another | ^ (caret) |
cat ^ mouse
|
A combination of search types | ( ) parentheses |
|
Configuring the Database
Once you have created the necessary databases, you can add new files to the databases, change some of the existing database file properties, and set the database configuration options appropriately.
Database File Configuration
You can change certain database file properties, as well as add or drop files using either of the following methods:
- use the Properties dialog box in Management Studio
- use the ALTER DATABASE command (for details, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads)
Adding Files
Data files can be added to an existing file group in a database, or to a new file group. There are no special restrictions or requirements.
Dropping Files
To drop a file, you must first empty it using the DBCC SHRINKFILE command’s EMPTYFILE option, which transmits the file data to all the other files in the file group. Once you empty the file, you can use the ALTER DATABASE <database name> DROP FILE command to drop it.
Changing File Properties
You can change the size-related properties for all databases, as well as the filename property for the tempdb database (this takes effect after you restart Microsoft SQL Server). The SIZE, MAXSIZE, and FILEGROWTH properties can be changed using the ALTER DATABASE tempdb MODIFY FILE command. Note that the SIZE property can only be enlarged.
To shrink the file, use the DBCC SHRINKFILE command. For details and recommendations concerning file properties, see Creating the Database.
Database Configuration Options
Each database contains a set of configurable options that determine its behavior. You can view or change the database options using any one of the following utilities:
- the Options tab in the Management Studio’s Properties dialog box
- the EXEC sp_dboptions stored procedure
- the ALTER DATABASE <database name> SET command
Note Not all of the database configuration options are available in this dialog box.
The following table lists, in alphabetical order, the default configuration options, as well as the configuration settings required for
Configuration Option | Description | Default |
|
---|---|---|---|
ANSI NULL default (see note below) | Specifies whether the database columns are defined as NULL or NOT NULL, by default | Not set | Not set |
ANSI PADDING | Controls the way the column stores values shorter than the defined size of the column and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. | OFF | ON |
Auto close | Specifies whether the database shuts down after its resources are freed and all users exit | Not set |
Not set Note If set, it may take a long time for the database to allocate resources every time a user connects, after the database is closed. |
Auto create statistics | Specifies whether missing statistics required by a query for optimization are built automatically during optimization | Set | Set |
Auto shrink | Specifies whether the database is automatically shrunk every hour, leaving 25% of free space | Not set |
Not set Note If set, constant growth/ shrinkage may cause file system fragmentation. |
Auto update statistics | Specifies whether out-of-date statistics required by a query for optimization are built automatically during optimization | Set | Set |
Compatibility level | The version of Microsoft SQL Server that the database appears to be (for the application) | The same version as the release installed | The same version as the release installed |
Read only | Database is read only | Not set (READ_WRITE) | READ_WRITE |
Recovery | The database recovery model determines the recovery capabilities by controlling the amount of bulk operation logging (such as Select into, Bulk, Insert, Create index, LOB manipulation). The higher the recovery model, the higher the recovery capabilities. However, the amount of logging also increases, which may affect performance. | Full | Full (unless you are certain that the lower recovery capabilities are sufficient for your system) |
Recursive triggers | Specifies whether recursive triggers are supported | Not set | Not set |
Restrict access | Only single users or members of the db_owner, dbcreator, or sysadmin groups can access the database. | Not set (MULTI_USER) | MULTI_USER |
Torn page detection | Specifies whether incomplete pages can be detected | Set | Set |
Truncate log on checkpoint | Automatically marks inactive portions of log for reuse on checkpoint | Not set | N/A |
Use quoted identifiers | Specifies whether the Microsoft SQL Server enforces ANSI rules regarding quotation marks. Select this option to specify that double quotation marks be used only for identifiers, such as column and table names. Note that character strings must be enclosed in single quotation marks. | Not set | Not set |
Note
Not all ANSI options can be set using Management Studio. The ANSI database configuration options include: ANSI_NULLS, ANSI_NULL_DEFAULT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER.
The options you set may not take effect, since these options can also be set at a higher level.
For example, if the session option QUOTED_IDENTIFIER was turned on, the equivalent database configuration option is irrelevant. Some tools or database interfaces turn certain session options on or off, so that the database configuration options never take effect.
The following table summarizes the characteristics of each recovery model:
Model/ Support | Allows Log Backup | Allows Point-in-Time/Log Mark Restoration | Allows Backup Log when Data Crashes (Saves changes until the crash point) | Amount of Bulk Operation Logging (can affect the performance of bulk operations) |
---|---|---|---|---|
Simple | No | No | No | Minimal |
Bulk Logged | Yes | No | No | Minimal |
Full | Yes | Yes | Yes | Full |
To check your database’s properties, run the command:
EXEC sp_helpdb <database name>
For information on SQL databases, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads.
Composite Indexes vs. Non Composite Indexes
For environments that execute heavy data-in operations, in order to speed up the INSERT statements, it is recommended to transform the database from composite index to non composite index.
You can achieve this by invoking the modifyCompositeIndexes JMX method with composite indexes setting to false. For detailed instructions, see How to Modify Composite Indexes.
We welcome your comments!
To open the configured email client on this computer, open an email window.
Otherwise, copy the information below to a web mail client, and send this email to cms-doc@microfocus.com.
Help Topic ID:
Product:
Topic Title:
Feedback: