DBCC PINTABLE

Application Parameters

A good technique for using table-driven parameters is the DBCC PINTABLE option. This option allows you to place the table containing your parameters into the data cache and keep it from being flushed, thereby increasing

performance of queries against this table.

DBCC PINTABLE marks a table, once it is used, to remain in the data cache until “unpinned.” DBCC PINTABLE does not read the table into cache;

instead, as the table is queried, the data pages are put into cache, then marked so they do not get flushed later. Once the table is pinned, changes to the data are logged, and the table can be recovered in the event of a media failure.

Use this feature with caution. If you pin a large table, it will consume your available data cache and impact the performance of queries against all your other tables. When storing your application variables on your SQL server, beware of creating a table that is high maintenance and low

return-on-investment. Create only the indexes needed and do not create a huge, wide, and hard-to-understand table structure.

Registry-Type Tables

There are two good methods for using server-based client application

http://www.itknowledge.com/reference/standard/1576101495/ch03/076-079.html (2 of 3) [1/27/2000 6:15:36 PM]

parameters. First is the creation of a registry-type table. This table allows you to store a broad range of data in a very flexible format. The Windows NT or Windows 95 Registry can be used as a model for this kind of table. Beware, however, of registry pitfalls. You have seen what can happen to a machine running Windows 95 or Windows NT when applications write to the registry in a haphazard way—disasters and the dreaded blue screen of death. Clean up after yourself and pay attention to keeping the data and keys clean and

up-to-date. Remove unused keys and perform maintenance on this table regularly. Store only single-value data in this type of structure.

A registry-type table does not lend itself to relational or set-based data queries.

Using a list-type table structure to return result sets to your client application is much more efficient.

List-Type Tables

A list-type table can be joined with other tables or used to return rows to a client application. One possible use of this kind of information might be a current user table. This table may store such information as the key values of the records the user is accessing and permission variables on those objects. By storing this information in a table, users can very easily be guided back to where they were last working, making the application appear to be smart and intuitive. Indexes play an even greater role in performance with this kind of table than with a normal data structure.

Beware of multiple indexes on this kind of table. Look at the queries that run against it and determine the minimum index configuration required. Updates are usually more frequent against this kind of table, and therefore the index values will need to be updated often.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch03/076-079.html (3 of 3) [1/27/2000 6:15:36 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book (Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Setup Scripts

A script is simply a file containing SQL commands that is executed against your SQL server to create, modify, or manipulate data or objects. Most large systems require you to run some sort of configuration scripts to prepare the system for operation. Very few good data-modeling tools are available that let you create a finished structure that takes into account revisions of objects and changes to permission structures. These types of issues are easily handled with scripts.

For example, scripts can be used to add the user-defined error messages for your application to the SQL server. Although these error messages can be configured by hand on a case-by-case basis, doing so lends itself to human error and inaccurately typed messages. Creating and executing a script file is much faster and allows you to configure two or more servers with exactly the same options. In fact, most data modeling tools allow you to create script files to run against your server for object creation.

When writing scripts, you should keep a few ground rules in mind:

1. A script is broken down into batches. These batches are designated by the word GO.

2. If an error occurs in a batch, that batch will not execute. Other batches in the script will still run, however.

3. You cannot create and reference an object in the same batch. You also cannot drop and create an object with the same name in the same batch.

4. SET statements take effect at the end of a batch.

5. Be sure to use a lot of comments in your scripts.

Although I will not go into writing SQL statements in detail until Chapter 5, these tips should be committed to memory. The above list will help you troubleshoot performance problems when executing scripts.

Sample Server Setup Script

In Listing 3.1, I’ve provided a sample setup script for adding or modifying user-defined messages. Notice that I have commented extensively in this script, even though it will be run only once in a while and should not change often. This allows other programmers to read and understand quickly what I was attempting to do with the script.

The /* and */ must be placed at the beginning and end of comments. Notice that the formatting shown here is for legibility only. Each of the EXEC lines is continued on the next line in this text (as indicated by indention) but should be on the same line in your script. See the sample scripts included on the CD-ROM for the actual

http://www.itknowledge.com/reference/standard/1576101495/ch03/079-082.html (1 of 3) [1/27/2000 6:15:37 PM]

Go!

Keyword

---Go!

text file.

Listing 3.1 Sample setup script.

/* ---Create user-defined error messages to be called by SQL statements and stored procedures with the system. Drop any existing messages in range to meet my needs.

/* The REPLACE option will overwrite existing message numbers */

/* General SQL Error */

EXEC sp_addmessage 60000, 10, "General SQL Error in <%s>. This will be logged in the Windows NT Eventlog.", us_english, TRUE, REPLACE /* Business Rule Violation */

EXEC sp_addmessage 60001, 10, "<%s> has violated business rule <%s>.

This will be written to the Windows NT Eventlog, and E-Mail will be sent.", us_english, TRUE, REPLACE

/* Missing E-Mail Address */

EXEC sp_addmessage 60002, 10, "There is no defined E-Mail recipient for Business Rule <%s>. This will be written to the Windows NT Eventlog.", us_english, TRUE, REPLACE

/* SQL Error In Procedure (Logged) */

EXEC sp_addmessage 60003, 10, "SQL Error %s in function ['%s'].

Attempting to %s.", us_english, TRUE, REPLACE

/* SQL Error In Procedure (Non-Logged) */

EXEC sp_addmessage 60004, 16, "SQL Error %s in function ['%s'].

Attempting to %s. This error can occur due to %s and will be ignored.", us_english, FALSE, REPLACE

/* Finished */

PRINT 'Finished.' GO

Setup scripts can be executed from a graphical interface or from the DOS Query tool provided with SQL Server. To use this sample script, you open the text file with any of the available text-based query tools, then execute it. The following is the sample output from this script when executed:

Replacing message.

New message added.

Replacing message.

New message added.

http://www.itknowledge.com/reference/standard/1576101495/ch03/079-082.html (2 of 3) [1/27/2000 6:15:37 PM]

Replacing message.

New message added.

Replacing message.

New message added.

Replacing message.

New message added.

Finished.

This sample is intended only to illustrate what a script looks like. I will explain in later chapters how to write scripts to perform different tasks on your server. Scripts are a very powerful tool that can be used to save a lot of time and ensure consistency among servers.

Another method of creating these scripts is to let SQL Server’s Enterprise Manager create them for you.

Although Enterprise Manager cannot script user-defined error messages, it can generate scripts for just about anything else.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch03/079-082.html (3 of 3) [1/27/2000 6:15:37 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Scripting Objects

You can use SQL-DMO (SQL Data Management Objects) and the SQL Enterprise Manager to generate scripts of objects that exist on your SQL server. You can select pre-existing objects in the Enterprise Manager and have the script created that would be necessary to drop and re-create the object on your server or any other SQL server. This can be very beneficial when you need to modify an existing object quickly.

Figure 3.2 shows the dialog box that appears when you select an object in the Server Manager window, then select the Object menu and the Generate SQL Script option. This is a very useful feature in a development environment. You have the ability to generate scripts for groups of objects, as well as for specific objects. Notice the Object Creation and Object Drop checkboxes. These

options allow you to drop and re-create objects from the script or scripts you generate.

Figure 3.2 The Generate SQL Scripts dialog box.

You might want to generate scripts to document the objects created by any third-party vendor’s software residing on your server. To do so, you could create a file with all the code essential to running other people’s software on your server. Remember that the Enterprise Manager will generate scripts that

http://www.itknowledge.com/reference/standard/1576101495/ch03/082-086.html (1 of 4) [1/27/2000 6:15:40 PM]

Go!

Keyword

---Go!

will drop and re-create objects, but it will not generate a script to migrate data from one structure to another. The only option available with the Enterprise Manager is the Transfer utility, accessed by right-clicking on a database and selecting the transfer item. I will go through this process in detail later in this chapter.

Third-Party Management

DBArtisan and the other utilities on the CD-ROM provide you with a few options for creating scripts. Embarcadero’s DBArtisan allows you to do a thorough job of scripting objects using the Extract button on the speed bar.

This tool gives you an alternative system-management tool for your Microsoft SQL Server.

DBArtisan is designed to manage multiple servers of various manufacturers from a single utility. This can be a great advantage over the Enterprise Manager. Some options listed and displayed may not be available for Microsoft SQL Server, so take the time to learn what can and cannot be accomplished with each tool. See Figure 3.3 for a sample of what the

DBArtisan product can offer you. Open the Microsoft SQL Server Enterprise Manager to compare features and functionality.

Figure 3.3 The DBArtisan work area.

Rapid SQL, also from Embarcadero, provides a fast scripting option that allows you to use objects in a list as a base for building your scripts.

Point-and-click graphic interfaces allow you to quickly select the objects you wish to modify or to create custom scripts with very little effort. Even if you are comfortable writing SQL in the standard text editor-like environment, these tools used by themselves or in conjunction with one another will provide you with the rapid development toolset that today’s MIS departments require.

Figure 3.4 shows the result of double-clicking on a table in the Table List window. Note the formatted and easy-to-read text.

Figure 3.4 The Rapid SQL work area.

In document Microsoft SQL Server Black Book pdf (Page 75-81)