Web Hosting Java Blog

174 Part I EXAM PREPARATION QUOTED_IDENTIFIER When set to ON, identifiers can be delimited by double quotation marks and literals must be delimited by single quotation marks. All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers do not have to follow the T-SQL rules for identifiers. They can be keywords and can include characters not generally allowed in T-SQL identifiers. When set to OFF (default), identifiers cannot be in quotation marks and must follow all T-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks. Identifiers must be enclosed in square brackets ([ ]) if they contain spaces or other characters or key words. READ_ONLY or READ_WRITE When READ_ONLY is specified, users can retrieve data from the database but cannot modify the data. Automatic recovery is skipped at system startup and shrinking the database is not possible. No locking takes place in read-only databases, which can result in faster query performance. When READ_WRITE is specified, users can retrieve and modify data. RECOVERY FULL or BULK_LOGGED or SIMPLE When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations, such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup. RECURSIVE_TRIGGERS When set to ON, triggers are enabled to fire recursively. When set to OFF (default), triggers cannot be fired recursively.

For high quality java hosting services please check java web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 173 CONCAT_NULL_YIELDS_NULL When set to ON, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. When set to OFF, concatenating a null value with a character string yields the character string as the result. CURSOR_CLOSE_ON_COMMIT When set to ON, any open cursors are closed automatically when a transaction using the cursor is committed. By default, this setting is OFF and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed, which is usually when a procedure finishes. CURSOR_DEFAULT LOCAL or GLOBAL When CURSOR_DEFAULT LOCAL is set, and a cursor is not defined as GLOBAL when it is created, the scope of the cursor is local to the batch, stored procedure, or trigger. The cursor name is valid only within this scope. When CURSOR_DEFAULT GLOBAL is set, and a cursor is not defined as LOCAL when created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch the connection executes. NUMERIC_ROUNDABORT If set to ON, an error is generated when the loss of precision occurs in an expression. When set to OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result. OFFLINE or ONLINE When OFFLINE is specified, the database is closed and shut down cleanly and marked offline. The database cannot be modified while it is offline. When ONLINE is specified, the database is open and available for use.

For high quality website hosting services please check tomcat web hosting website.

172 Part I EXAM PREPARATION ARITHABORT When set to ON, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When set to OFF, a warning message is displayed if one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred. AUTO_CLOSE When set to ON, server resources are freed up as soon as the database is closed and shut down cleanly when the last user of the database exits. By default, this option is set to ON for all databases in the Desktop Engine, and OFF for all other editions. The database reopens automatically when a user tries to use the database again. When set to OFF, the database remains open even if no users are currently using it. AUTO_CREATE_STATISTICS When set to ON, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the optimizer can better determine how to evaluate queries. If the statistics are not used, SQL Server automatically deletes them. When set to OFF, SQL Server does not automatically create statistics; instead, statistics can be manually created. AUTO_SHRINK When set to ON, the database files are set up for periodic shrinking. Any database-associated file, data, or log can be shrunk automatically. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases in SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system. AUTO_UPDATE_STATISTICS When set to ON, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When set to OFF, existing statistics are not automatically updated; instead, statistics can be manually updated.

For high quality website hosting services please check java web hosting website.

172 Part I EXAM PREPARATION ARITHABORT When set to ON, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When set to OFF, a warning message is displayed if one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred. AUTO_CLOSE When set to ON, server resources are freed up as soon as the database is closed and shut down cleanly when the last user of the database exits. By default, this option is set to ON for all databases in the Desktop Engine, and OFF for all other editions. The database reopens automatically when a user tries to use the database again. When set to OFF, the database remains open even if no users are currently using it. AUTO_CREATE_STATISTICS When set to ON, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the optimizer can better determine how to evaluate queries. If the statistics are not used, SQL Server automatically deletes them. When set to OFF, SQL Server does not automatically create statistics; instead, statistics can be manually created. AUTO_SHRINK When set to ON, the database files are set up for periodic shrinking. Any database-associated file, data, or log can be shrunk automatically. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases in SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system. AUTO_UPDATE_STATISTICS When set to ON, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When set to OFF, existing statistics are not automatically updated; instead, statistics can be manually updated.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 171 These options are described in the following sections, and are covered in alphabetical order. ANSI_NULL_DEFAULT This option enables the user to control the default nullability. When NULL or NOT NULL is not specified, a user-defined data type or a column definition uses the default setting for nullability. When this option is set to ON, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting. ANSI_NULLS When set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF. ANSI_PADDING When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks and zeros are trimmed. This setting affects only the definition of new columns. It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views. ANSI_WARNINGS When set to ON, errors or warnings are issued when conditions such as divide by zero occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as divide by zero occur. By default, ANSI_WARNINGS is OFF.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

170 Part I EXAM PREPARATION Configuration Exam Considerations The SQL Server configuration options are used to fine-tune the database environment. Many options provide a mechanism for an administrator or developer to obtain optimum performance and achieve a more secure and stable server. A total approach to an optimum environment also involves the proper use of database configuration options. Server property adjustments affect all the databases stored on the server where database configuration options are used to control a database and not affect other databases. Setting Database Configuration Options Standard database configuration settings are available through the Database Properties in the Enterprise Manager or can be accessed using the sp_dboption stored procedure. Some of the more advanced settings cannot be set singly; they must be set in combination with other settings. There are five categories of database options: Auto options. AUTO_CLOSE, AUTO_CREATE_ STATISTICS, AUTO_UPDATE_STATISTICS, and AUTO_SHRINK Cursor options. CURSOR_CLOSE_ON_COMMIT and CURSOR_DEFAULT LOCAL or GLOBAL Recovery options. RECOVERY FULL or BULK_LOGGED or SIMPLE and TORN_PAGE_DETECTION SQL options. ANSI_NULL_DEFAULT, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, NUMERIC_ROUNDABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_ IDENTIFIER, and RECURSIVE_TRIGGERS State options. OFFLINE or ONLINE, READ_ONLY or READ_WRITE, SINGLE_USER or RESTRICTED_USER or MULTI_USER and WITH ROLLBACK AFTER or WITH ROLLBACK IMMEDIATE or NO_WAIT.

For high quality website hosting services please check cheap web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 169 options dynamically for new logins. After you change the setting of user options, new logins use the new setting; current logins are not affected. Reconfigure This option updates the server configuration. It is used after the application of sp_configure to change server settings and make the new settings take effect. Because some configuration options require that a server stop and restart before the currently running value can be updated, Reconfigure does not always update the currently running value. Use the With Override option of this command to force a value that might or might not meet ranges of allowed values or recommended settings. Changing configuration options can easily be performed using T-SQL operations: TSQL USE master EXEC sp_configure show advanced option , 1 RECONFIGURE GO EXEC sp_configure recovery interval , 3 RECONFIGURE WITH OVERRIDE Step by Step 3.8 takes you through the setting of some common server options. STEP BY STEP 3.8 Setting Server Configuration Options 1. Expand a server group, then right-click the server, and then select Properties. 2. Select the Database Settings tab. 3. Set the Recovery Interval to 3. 4. Select OK to save the setting.

For high quality website hosting services please check tomcat web hosting website.

168 Part I EXAM PREPARATION Remote Query Timeout This option is used to indicate the number of seconds that must elapse when processing a remote operation before the operation times out. The default of 600 sets a ten-minute wait. Scan for Startup Process (Advanced) Use this option to scan for automatic execution of stored procedures at startup time. If it is set to 1, SQL Server scans for and executes all automatically executed stored procedures defined on the server. The default value is 0 (do not scan). Set Working Set Size (Advanced) Reserves physical memory space for SQL Server that is equal to the server memory setting. SQL Server, based on workload and available resources, configures the server memory setting automatically. It varies dynamically between the Min Server Memory and Max Server Memory settings. Two Digit Year Cutoff Use the Two Digit Year Cutoff option to specify an integer from 1753 to 9999 that represents the last year for interpreting two-digit years as four-digit years. User Connections (Advanced) Use this option to specify the maximum number of simultaneous user connections. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application(s) and hardware. SQL Server enables a maximum of 32,767 user connections. User Options The User Options option is used to specify global defaults for all users. A list of default query processing options is established for the duration of a user s work session. A user can override these defaults by using the SET statement. You can configure user

For high quality website hosting services please check cheap web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 167 Query Wait (Advanced) Memory-intensive queries, such as those involving sorting and hashing, are queued when not enough memory is available to run the query. The query times out after a set amount of time that SQL Server calculates (25 times the estimated cost of the query) or the time amount specified by the non-negative value of the query wait. Recovery Interval (Advanced) Use this option to set the maximum number of minutes per database that the server needs to recover the database activity. The recovery process is initiated each time SQL Server starts or as the basis for completing a restore operation. The recovery process rolls back transactions that did not commit and rolls forward transactions that did commit. This configuration option sets an upper limit on the time it should take to recover each database. The default is 0, indicating automatic configuration by SQL Server. In practice, this means a recovery time of less than one minute and a checkpoint marker is placed into the transaction log approximately every one minute for active databases. Remote Access Use the Remote Access option to control logins from remote servers running SQL Server. Set Remote Access to 1 (default) to enable logins from remote servers. Set the option to 0 to secure a local server and prevent access from a remote server. Remote Login Timeout Use this option to specify the number of seconds to wait before returning from a failed remote login attempt. Remote Proc Trans This option protects the activities of a server-to-server process through the use of the Distributed Transaction Coordinator. Set Remote Proc Trans to 1 to provide an MS DTC-coordinated distributed transaction that protects the ACID properties of transactions. Sessions begun after setting this option to 1, inherit the configuration setting as their default.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

166 Part I EXAM PREPARATION Min Memory Per Query (Advanced) Use this option to specify the minimum amount of memory that will be allocated for the execution of a query. Nested Triggers The Nested Triggers option enables actions that initiate another trigger to be performed. When the Nested Triggers option is set to 0, triggers cannot cascade. When the Nested Triggers option is set to the default setting of 1, triggers can cascade to as many as 32 levels. Network Packet Size (Advanced) Use this option to set the packet size used across the entire network. The default packet size is 4096 bytes. If an application does bulk copy operations, or sends or receives large amounts of text or image data, a packet size larger than the default can improve efficiency because it results in fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers. Open Objects (Advanced) Use this option to set the maximum number of database objects that can be open at one time. Database objects are those objects defined in the sysobjects table: tables, views, rules, stored procedures, defaults, and triggers. Priority Boost (Advanced) This option specifies the processor scheduling priority. If you set this option to 1, SQL Server runs at a priority base of 13 in the Windows NT 4.0 or Windows 2000 Scheduler. The default is 0, which is a priority base of 7. Query Governor Cost Limit (Advanced) Specifies an upper limit for the time in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to execute a query.

For reliable and cheap web hosting services please check tomcat web hosting website.