The Need for a More Complex Design


Sometimes you need something more than the data alone can provide. A different perspective on the data may be needed to simplify matters or supply a security layer between the user and the data store. This functionality is provided by views.

In an attempt to achieve as little downtime as possible, it is essential to consider a strategy that involves multiple servers and redundant other hardware. Data redundancy, adequate backups, and some form of disaster recovery plan must all be a part of a complete solution.

Although most of the topics surrounding server clustering fall out of the scope of this book, the partitioned views are discussed at length in the section "Other Multiple Machine Database Implementations," later in this chapter. This functionality includes such topics as data replication, partitioned views, and log shipping.


User Interaction Through Views

A view is a predefined SELECT statement that creates a dynamic virtual table. Although not a real table, a view can be used for many tasks for which an actual table can be used and is often referenced in the same way as a table. A VIEW can be used in SELECT, INSERT, UPDATE, and DELETE statements as if it were a table. No data is stored within views (except indexed views).

Often you would like to design an application that gives the user a list of specific columns out of a table but does not grant the user access to all data. A view can be used to limit what the user sees and the actions the user can perform over a portion of the data in a table.

An alternative to creating a view would be to handle column-level permissions over a table, which can be a true nightmare to administer. A new interface feature in SQL 2000 does enable you to use the GUI to set column-level permissions. However, this feature should be used as little as possible, if ever, because handling permissions at a granular level will lead to administrative headaches.

The problem with column-level permissions is that the initial creation process of the permission is time-consuming, and the granularity of maintenance of the permissions requires extremely careful documentation. Imagine a table with 100 columns and 1,000 or more users, groups, and roles. Trying to document and keep track of all the permissions is an immense task that will overwhelm even the best administrator.

Use a view to simplify administration and provide a more meaningful perspective on the data for the user. The following example shows the creation of a view:

 CREATE VIEW InStock AS SELECT ProductID, Description, QTYOnHand FROM Products WHERE QTYOnHand > 0 

In the preceding example the view limits the data to items that are in stock. If it were used in place of the entire table, when displayed onto a sales terminal, it would reduce errors in attempting to order items that were not in stock. View access permission can also be granted to specific users throughout your database, which produces a flexible security management plan. Views can be used to define numerous combinations of rows and columns from one or more tables. When views use only a few of the columns in a table, the table data is referred to as being vertically filtered; and when views use only a portion of the rows in a table, the table data is referred to as being horizontally filtered. The capability to filter data is a great advantage of using views. If only a subset of data is desired, a view prevents users from seeing too much data.

The concept of filtering data can be taken further because views can be used with horizontally or vertically partitioned data through a partitioned view. This is where data that could have been put into one table is split into several tables to improve performance. A view can then be used to join all this data together. If the data is on different servers, you are working with a distributed partitioned view. Server Federations utilize horizontal and/or vertical partitioning of data across several servers.

On the largest of databases you will need multiple servers to achieve the capacity and performance needed in a business scenario. In this type of system, a multiple server system will be needed to balance the processing load for the database. SQL Server 2000 shares the database processing load across a group of servers by horizontally partitioning the SQL Server data. These servers are managed independently, but they cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

A view can contain 1,024 columns that are extracted from a single table or multiple tables, and they have other restrictions, explored later in this chapter. A view can return an unlimited number of rows, so the number of rows is dependent on the number of rows in the table or tables referenced and the combination of filters applied.

Views provide many benefits and, because of this, are very common throughout an enterprise database environment. The number-one reason a view is created is to protect data from inquisitive eyes. This means that the developer has to worry only about allowing access to the view and further restricting the rows that are returned. Views provide many other benefits, including the following:

  • Make querying easier. Views enable users to execute specific SELECT statements without requiring users to provide the entire SELECT statement each time it executes.

  • Hide irrelevant data. Views enable you to use SELECT to select only the data that is needed or of interest.

  • Enforce security. Users can view only what you let them see. This may be a set of rows or columns or both. This feature is especially important when sensitive information is involved, such as salary and credit card information. Views provide a more efficient data retrieval process and easier security management because permissions are checked and maintained at only one level.

  • Export data easily. Views can gather data from different views and tables, thus making it easy to export data. This data can be exported using the Bulk Copy Program (BCP) or Data Transformation Services (DTS). Regardless of the tool you are using, it is easier to create an export statement if you can tell it to take all the data in a specific table. The view can be used to consolidate this data for this purpose.

Views offer some great advantages but have several restrictions that do not exist in normal SELECT statements. Before examining the view creation process, you should review some facts and restrictions that deal with views. SQL Server will not let you forget these facts when you are creating your views; it throws up an error, usually detailing what you are missing or doing wrong. The most important of these facts, other than those already mentioned, are the following:

  • The view's definition can comprise rows and columns from one or more tables in the current or other databases.

  • Defaults, triggers, and rules are not allowed to be associated with a view. The only exception to this rule is the new INSTEAD OF trigger.

  • View names must follow the rules for identifiers.

  • Views cannot be created using the ORDER BY clause unless they use the TOP clause (as in TOP 100 PERCENT).

  • Views cannot be created using the COMPUTE BY or SELECT_INTO clauses.

  • View names must adhere to uniqueness rules.

  • Views cannot be created on temporary tables.

  • Temporary views cannot be created.

  • Views can be nested up to 32 levels.

Views can be created using the Enterprise Manager, the Create View Wizard, or the T-SQL CREATE VIEW statement. You will usually want to filter only a portion of a table selected with the view. A good example of this might be when you want to hide a salary column of a table, and therefore create a view that references all columns except the salary column.

For designing views of all types, you may find it advantageous to use the View Designer, accessible from the Enterprise Manager when you select Create View of New View from within a database. The designer is presented in Figure 4.1, showing a view that could be used to access orders that have no discounts applied.

Figure 4.1. The View Designer accessed from Enterprise Manager.


Notice from Figure 4.1 that the syntax of the view is created as you work with the designer. Also notice that the criteria element on the Discount line shows =0, which would represent horizontal filtering with the use of a WHERE clause.

You can create view definitions beyond a standard SELECT statement. Utilizing views, you can join multiple tables, use aggregate functions, or combine the use of user-defined functions to make capable things normally restricted within views. These are really just features of more advanced query techniques, which will be discussed at length in Chapter 5, "Retrieving and Modifying Data."

Getting a Better View

A resultset can use joins to gather data from a number of tables. You can reference up to 256 tables in a single SELECT statement or view. You can make use of the power and versatility of data selection to essentially provide any information from the data.

Similar to the way in which a table serves as the base for a view, a view can gather its information from another view. Creating a view using an existing view as the underlying information source helps when you want to further refine criteria on an existing view. To create a view referencing a base view, examine the following code listing. If we were interested only in obtaining a list of employees from the United States, our previously created EmployeeListing view could be used as the basis for another view, as shown here:

 CREATE VIEW AmericanEmployees AS SELECT LastName, FirstName, Address, City,                 PostalCode, Country, Region, HomePhone FROM EmployeeListing WHERE Country = 'USA' 

You may need to change a view definition. You accommodate this by dropping and re-creating the view, but doing so would reset any previously granted permissions. Using the ALTER VIEW statement, you can easily reshape the definition of a view without affecting the permissions granted. Altering the AmericanEmployees view to include an employee ID could be performed as shown here:

 ALTER VIEW AmericanEmployees AS SELECT EmployeeID, LastName, FirstName, Address, City,                 PostalCode, Country, Region, HomePhone FROM EmployeeListing WHERE Country = 'USA' 

Don't be careless with the changes to views. Altering views or tables that are used by views may cause the dependent views to stop working. SQL Server does not back-check all table or view alterations to ensure that they do not create errors. This is where the SCHEMABINDING option helps out. If you have SCHEMA-bound objects, you cannot ALTER the source objects at all. It may be preferable to drop and re-create a series of objects, rather than having objects that do not function. This process is defined later in the chapter, in the section "Indexed Views."

You may find that SQL Server does not allow you to perform tasks because of one dependency or another. For instance, all objects are dependent on their owner, so you are not able to drop a user from the database if that user owns objects. The number of database objects that the server checks for dependencies is very large, but it is easy to find out what objects they are. A quick query of sysobjects can reveal any object ownership dependencies. To remove a view from a database, use the DROP VIEW statement. Dropping a view removes the definition of the view from the database and an entry in the sysobjects, while not affecting the underlying tables and views. If object deletions are performed from the Enterprise Manager, the dependencies can be viewed before removal, as shown in Figure 4.2.

Figure 4.2. Enterprise Manager dependency listing.


Scripts, stored procedures, and views can exist anywhere, and refer to tables or views in your database. There is no easy location that can be checked that tells SQL Server who is referencing your table or view. Another user can reference your view from an entirely different server, without your knowledge, if that user has been granted the SELECT permission to it. Therefore, there is no back-checking of integrity when you change the structure of tables or views.

Utilizing Views

Views serve many useful purposes in a database system. As previously mentioned, a view can be substituted for a table in most instances, but this is only the beginning of where you can apply a view to solve a business problem. Data can be viewed, modified, inserted, and deleted through the use of views. Views are extremely useful in extracting desired data to be ported to other systems. There are a few items to keep in mind when you are developing a system around views. When modifying data in a view, you cannot alter any calculated result; there is no capability to make the changes to the data that provided the derived information. In a view that contains more than one table, the data changed can affect only one table at a time. Altering data in both tables of a two-table view would require two updates.

Any inserts performed against the underlying table must provide values for all NOT NULL columns, unless DEFAULT values are declared for those columns. Any inserted data must also conform to any constraints that are in place and may affect the newly added data. It is possible that a definition could let you INSERT data that is not actually visible through the view. Views can partition data through conditional operation or otherwise limit output using table joins. If you want to prevent the INSERT of data that does not conform to the view, you can use the WITH CHECK OPTION when creating your view.

There are many view options on the exam. You could easily see an exam question that asks you to ensure that the data seen, deleted, inserted, and updated through a view conforms to the view definition. Remember that one of the purposes of a view is to hide and/or protect the underlying data.


You can easily see the power of using views in many instances. As long as a few simple conventions are followed, views are a flexible and commanding tool in the developer's arsenal. But we have only scratched the surface, and we still have a few major areas of views to examine. With these additional topics, come many more idiosyncrasies that can easily be exam material.

Indexed Views

In a standard view there is a great deal of overhead associated with the view object operating over the data that is stored in a table. The view must dynamically build the information from the table(s) each time the view is accessed. The overhead can be considerable in views that involve complex processing of a large amount of data. In cases in which these views are frequently needed in a business solution, you can improve performance by using a clustered index. When a unique clustered index is created on a view, the view is executed and the resultset is stored in the database in the same way a table with a clustered index is stored. Indexed views are available only through the use of the Enterprise version of SQL Serverother versions of the software do not allow the option. If you want to use indexed views, a number of session-level options must be set to ON when you create the index. You need to set NUMERIC_ROUNDABORT to OFF. The options that need to be set to ON are listed here:

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER

Become comfortable with these configuration options. You are likely to find exam questions about the options needed to set up indexed views. Ensure that you are comfortable with the configuration required.


Schema Binding

Note that SCHEMABINDING has to be specified when you create indexed views. Also, when SCHEMABINDING is specified, you have to adhere to the owner.object syntax when referencing tables or views in the creation of your view. A lot of specific options need to be in place to allow for indexed views to be defined.

Schema binding involves attaching an underlying table definition to a view or user-defined function. Normally, if this process is not used, a function or view definition does not hold any data or other defining characteristics of a table. The definition is stored as a set of T-SQL statements and handled as a query or procedure. With binding, a view or function is connected to the underlying objects. Any attempt to change or remove the objects fails unless the binding has first been removed. Normally, you can create a view, but the underlying table might be changed so that the view no longer works. To prevent the underlying table from being changed, the view can be "schema-bound" to the table. Any table changes, which would break the view, are not allowed.

The word schema has several different uses and definitions within SQL Server; the exam will leverage this fact and attempt to confuse the separate definitions. Make sure that you are aware of how the term is used in relation to XML, indexed views, and maintaining metadata. For more information about these particulars, you can consult Chapter 5 in the "Extensible Markup Language" section; this chapter, in the "Indexed Views" section; and Chapter 7, "Tuning and Optimizing Analysis," in the "Information Gathering" section.


Indexed views, defined previously in the chapter, require that a view be defined with the binding option and also that any user-defined functions referenced in the view also be bound. In previous versions of SQL Server, it was not possible to define an index on a view. With the advent of binding, however, meaningful indexes can now be defined over a view that has been bound to the underlying objects. The following example uses T-SQL for the creation of a schema-bound view:

 CREATE VIEW SampleBoundView WITH SCHEMABINDING AS           SELECT ProductID, Description, PurchPrice,   PurchPrice * Markup AS SalesPrice           FROM dbo.ProductTable 

Other than setting the specific set of options, nothing more needs to be done for the optimizer to utilize an index with a query on a view. Essentially, the SQL Server Optimizer handles the view query in the same manner in which it would handle a standard query against a table. The view cannot reference another view; only underlying tables are permitted, and you must create the view with the SCHEMABINDING option. Only the Enterprise and Developer editions support the creation of an indexed view.

There are some limitations to the content of the SELECT statement for the view definition:

  • There may be no use of *.

  • A column name used as a simple expression cannot be specified in more than one view column.

  • There may be no derived tables.

  • Rowset functions are not permitted.

  • UNION, outer joins, subqueries, or self-joins cannot be usedonly simple joins can be used.

  • There may be no TOP, ORDER BY, COMPUTE, or COMPUTE BY clause.

  • DISTINCT is not permitted.

  • COUNT(*) cannot be used, but COUNT_BIG(*) is allowed.

  • The aggregate functions AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP are not permitted.

  • A SUM function cannot reference a nullable expression.

  • The full-text predicates CONTAINS or FREETEXT may not be used.

We have introduced a lot of new terms in this list. These terms are new in our discussion but will be revisited later in the book.


Note that although indexed views are advantageous for performance reasons, in some cases they can not be utilized if greater functionality is desired within the information presented by the application. Another performance consideration that usually comes into play with extremely large datasets is the use of partitioned views.

Partitioned Views

A partitioned view enables the creation of a view that spans a number of physical machines. These views can fall into one of two categories: local or distributed. A distinction is also made between views that are updatable and those that are read-only. The use of partitioned views can aid in the implementation of federated database servers, which are multiple machines set up to share the processing load.

Multiple server operations balance the load so that updates are potentially separated from queries and query load can be spread across multiple machines. For more information on federated server implementations, see SQL Server Books Online, "Designing Federated Database Servers." Federated Servers are beyond the scope of the exam and therefore this book as well.

Partitioned views drastically restrict the underlying table designs and require several options to be set when indexes are used. Constraints need to be defined on each participating server so that only the data pertaining to the table(s) stored on that server is handled. Although CHECK constraints are not needed to return the correct results, they enable the query optimizer to more appropriately select the correct server to find the requested data.

To use partitioned views, you horizontally split a single table into several smaller tables, each having the same column definitions. Set up the smaller tables to accept data in ranges and enforce the ranges using CHECK constraints. Then you can define the distributed view on each of the participating servers. Add linked server definitions on each of the member servers. An example of a distributed view definition is as follows:

 CREATE VIEW AllProducts AS            Select * FROM Server1.dbo.Products9999 UNION ALL            Select * FROM Server2.dbo.Products19999 UNION ALL            Select * FROM Server3.dbo.Products29999 

Partitioning attempts to achieve a balance among the machines being used. Data partitioning as defined previously involves the horizontal division of a singular table into a number of smaller tables, each dealing with a range of data from the original and split off onto separate servers. Some configuration options can help gain performance when operating against partitions. Setting the Lazy Schema Validation option using sp_serveroption can optimize performance. When used, it is set for each linked server definition. This optimizes performance by ensuring that the query processor does not request metadata for any of the linked tables until actually needed. Attempting to ensure that the correct query goes to the appropriate server also helps to improve performance while minimizing bandwidth use.

A partitioned view is considered to be updatable if a set of SELECT statements is combined into one resultset using UNION ALL operations, as shown previously in this section. Indexes based on calculated columns are not permitted within any table definitions, and all tables must have a primary key and an ANSI_PADDING set.

When you use a partitioned view to insert data, all columns must be included in the INSERT statement, even if the table definitions provide DEFAULTs or allow for NULL content. Also, IDENTITY columns cannot be referenced; therefore, no underlying tables can have IDENTITY columns, nor are they permitted to have timestamp columns.

Remote partitioned views require that you keep a few additional considerations in mind. A distributed transaction is automatically initiated to ensure that integrity is maintained throughout all operations, and the XACT_ABORT option must be set to ON. Smallmoney and smalldatetime data types in the remote tables are mapped to money and datetime types locally.

Partitioning Strategies

Partitions can be designed in a symmetric or asymmetric fashion, and although it is most useful to design symmetrically, the access requirements of a lot of systems necessitate an asymmetric design.

A symmetrical design is one in which all related data is placed on the same server so that most queries do not have to cross network boundaries to access the data. It is also easier to manage the data if the division of tables can be performed in such a manner that each server has the same amount of data. In most real-world applications, data is accessed in a random fashion that can make the designer lean toward an asymmetric implementation. The design can be configured so that one server has a larger role and/or contains more data than the others. Performance can be improved if you weigh each server's use and make one server work harder on the partitioned applications, because the other servers perform larger tasks that deal with other unrelated processes.

Designing for distributed partitioned views requires appropriate planning of front-end applications to ensure that, whenever possible, data queries are sent to the appropriate server. Middleware, such as Microsoft Message Queue or an application server or other third-party equivalents, should attempt to match queries against data storage. When preparing for data communication with the front-end application, the operating-system settings of the server will affect the server's interaction with the application.

Operating-System Service Properties

There are four services acting within the operating system that provide the functionality to the database system. The SQL Server Service is responsible for data retrieval and any other interactions with the data. The SQL Server Agent Service is responsible for the execution of scheduled tasks and the performing of maintenance activities. The Microsoft Search Service is a full-text indexing and search engine that is an optional portion of a server installation. Text-searching capabilities can be used to find data matches when searching using English text and phrases. Finally, the Distributed Transaction Coordinator Service is optionally used to control interactions between multiple servers. For any of the features provided by these services to be utilized, they must be installed and running on the server.

In SQL Server 2000, two aspects of the server's operating system allow for a successful service restart in the event of failure. The operating system's services can be configured to automatically start on computer startup and can also be set up to respond to service interruptions. To set service properties, you must locate the MSSQLServer Service. This service can be found in your administrative tools, Services on a Windows 2000 or 2003 operating system, or Control Panel Services for an NT-based system.

For the configuration options as displayed when using the Windows 2000 services properties, see Figures 4.3. Besides configuration through the operating-system services interface, there are also settings that are accessible from the Enterprise Manager that will affect the server's operations. These settings can also be controlled through the use of T-SQL.

Figure 4.3. Configuring Windows 2000 Services.


The database recovery interval can be set for the number of minutes each database takes to start up after an outage or controlled server startup. You can find the Recovery Interval option in the Enterprise Manager by right-clicking the server, selecting Properties from the pop-up menu, and navigating to the Database Settings tab.

User accounts should be assigned for the SQL Server and SQL Server Agent services. Separate user accounts can be identified for each of the services. Conversely, the same account can be used for all services and several servers. As a standard implementation, it is usually best to use the same account. You might want to use separate accounts for each server, particularly if you want each server to send and receive email as a separate identity.

Server Configuration Settings

Standard configuration settings are available through the Server Properties dialog box in the Enterprise Manager, or they can be accessed using the sp_configure stored procedure. Descriptions of these configuration settings are presented in the subsections that follow. Some of the more advanced options require that you enable Show Advanced Options. You can set advanced options on by using the following:

 sp_configure 'show advanced options', 1 reconfigure 

You won't need to know all the server and database configuration options on the exam. On the two SQL Server MCDBA exams, though, you will be confronted with a number of them, so having a good idea as to what most of them are used for is strongly recommended.


Affinity Mask (Advanced)

Use the Affinity Mask option in systems that have four or more processors. It increases performance when the system is under a heavy workload. You can specify which processors Microsoft SQL Server is to use. You can exclude SQL Server activity from processors that have been given specific workload assignments by the Windows NT 4.0 or Windows 2000 operating systems.

Allow Updates

The Allow Updates option is used to allow the direct alteration of system tables. When Allow Updates is set to 1, any user with appropriate permissions can either update system tables directly with ad-hoc updates or create stored procedures that update system tables.

AWE Enabled (Advanced)

Address Windowing Extension (AWE) is an advanced option used to support up to a maximum of 64GB of physical memory.

C2 Audit Mode

Use C2 Audit Mode to review both successful and unsuccessful attempts to access statements and objects. Allowing for the documentation of system activity and observance of security policy violations, C2 auditing tracks C2 audit events and records them to a file in the \mssql\data directory or the \mssql$instancename\data directory for named instances of SQL Server. If the file reaches a size limit of 200MB, C2 auditing starts a new file.

Cost Threshold for Parallelism (Advanced)

Use the Cost Threshold for Parallelism option to specify the threshold at which SQL Server creates and executes parallel query plans. Parallel query plans are executed only when the estimated cost to execute a serial plan for the same query is higher than the value set. The cost refers to an estimated elapsed time in seconds that is required to execute a standard plan. Set Cost Threshold for Parallelism only on symmetric multiprocessors.

Cursor Threshold (Advanced)

Use the Cursor Threshold option to indicate the number of rows in the cursor set at which cursor keysets are generated asynchronously. If you set Cursor Threshold to -1, all keysets are generated synchronously, which benefits small cursor sets. If you set Cursor Threshold to 0, all cursor keysets are generated asynchronously. With other values, the query optimizer compares the number of expected rows in the cursor set and builds the keyset asynchronously if it exceeds the number set in Cursor Threshold. Do not set Cursor Threshold too low because small resultsets are better built synchronously.

Default Language

Use the Default Language option to specify the default language for all newly created logins.

Fill Factor (Advanced)

Use the Fill Factor option to specify how full the server should make each page when it creates a new index using existing data. The Fill Factor percentage affects performance because SQL Server must take time to split pages when they fill up. The default for Fill Factor of 0 (zero) does not mean that pages are 0% full. It is treated similarly to a fill factor value of 100 in that indexes are created with full data pages and nonclustered indexes with full leaf pages. The default setting is different from 100 in that SQL Server leaves some space within the upper level of the index tree.

Index Create Memory (Advanced)

Use the Index Create Memory option to control the amount of memory used by index creation sorts. This option is self-configuring and should operate without requiring adjustment. If difficulties are experienced creating indexes, consider increasing the value. Query sorts are controlled through the Min Memory Per Query option. The default value for this option is 0 (self-configuring).

Default Full-Text Language (Advanced)

Use the Default Full-Text Language option to specify a default language value for full-text indexed columns. The default value of this option is the language of the server.

Lightweight Pooling (Advanced)

The Lightweight Pooling option provides a means of reducing the overhead associated with the excessive context switching sometimes seen in multiprocessor environments. When excessive context switching is present, Lightweight Pooling might provide better throughput.

Locks (Advanced)

The Locks option sets the maximum number of available locks, limiting the amount of memory the server uses. The default setting is 0, which enables SQL Server to allocate and deallocate locks dynamically based on changing system requirements.

Max Degree of Parallelism (Advanced)

The Max Degree of Parallelism option limits the number of processors to use in parallel plan execution. The default value is 0 (the actual number of CPUs) and the maximum is 32.

Max Server Memory/Min Server Memory

These two settings, Max Server Memory and Min Server Memory, establish upper and lower limits to the amount of memory the database engine uses. The database engine starts with only the memory required to initialize. As the workload increases, it acquires additional memory. The database engine frees any of the acquired memory until it reaches the amount specified in Min Server Memory.

Max Text Repl Size

The Max Text Repl Size option specifies the maximum size (in bytes) of text and image data that can be added to a replicated column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement.

Max Worker Threads (Advanced)

The Max Worker Threads option configures the number of worker threads available to the server and its processes. SQL Server uses the threads so that one or more threads simultaneously support each network that SQL Server supports; another thread handles database checkpoints; and a pool of threads handles user connections.

Media Retention (Advanced)

Use the Media Retention option to provide a default for the length of time each backup should be retained. Overridden by the RETAINDAYS clause of the BACKUP statement, Media Retention helps protect backups from being overwritten until the specified number of days has elapsed.

Min Memory Per Query (Advanced)

Use the Min Memory Per Query 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 performance of another trigger. 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 the Network Packet Size 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 the Open Objects 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)

The Priority Boost 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)

The Query Governor Cost Limit option 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.

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 the Recovery Interval option to set the maximum number of minutes per database that the server needs in order 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 (the 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 the Remote Login Timeout option to specify the number of seconds to wait before returning from a failed remote login attempt.

Remote Proc Trans

The Remote Proc Trans 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.

Remote Query Timeout

The Remote Query Timeout 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 10-minute wait.

Scan for Startup Process (Advanced)

Use the Scan for Startup Process 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)

The Set Working Set Size option 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 the User Connections 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 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

The Reconfigure 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.

The SQL Server configuration options are used to fine-tune the database environment. Many options provide a mechanism for an administrator or a 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.



    EXAM CRAM 2 Designing and Implementing Databases with SQL Server 2000 Enterprise
    MCAD/MCSE/MCDBA 70-229 Exam Cram 2: Designing & Implementing Databases w/SQL Server 2000 Enterprise Edition
    ISBN: 0789731061
    EAN: 2147483647
    Year: 2005
    Pages: 154

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net