MCTS Exam Cram: Implementing Database Objects

This chapter is from the book

MCTS 70-431 Exam Cram: Implementing and Maintaining Microsoft SQL Server 2005 Exam

Learn More Buy

This chapter is from the book

This chapter is from the book 

MCTS 70-431 Exam Cram: Implementing and Maintaining Microsoft SQL Server 2005 Exam

Learn More Buy

Using System Tables and Views

SQL Server tracks information and maintains data about every object in the system. This information is maintained in system tables that can be queried like any other tables. Of course, it helps to know which table to query because sifting through the information in the system tables can be an arduous task. Microsoft has already gone to the effort of preparing some of the most desired information. You can see this information by querying views. You can produce still other information from recordsets of data displayed by executing system stored procedures.

If you look behind the scenes into these views and procedures, you are likely to see nothing more than the system tables being queried and the information being presented in a more readable fashion.

Still, nothing beats a little bit of know-how or, in this case, know-where. There are some useful system tables that you might want to query from time to time to double-check definitions.

Getting Information from System Tables

We have already discussed most of the actual object definitions, and if you look into the master database, you can find the storage area for these definitions. Querying the master database allows you to get information from the system. Views have replaced system tables and are prefixed with sys stored object definitions. There are many system views, but the ones described in Table 3.2 are the most commonly accessed and useful for development purposes.

Table 3.2. Common System Views/Tables

In SQL Server 2005, each of the tables now maps to a system view that can be queried in place of the table. Although it is possible through some advanced configuration, the system tables should not be changed directly. You should never try to modify system tables by using DELETE, UPDATE, or INSERT statements or user-defined triggers.

In SQL Server 2000 and earlier versions, these views were system tables. For backward compatibility, they are still available. Each view, however, has a replacement system view that you should use going forward. For each of the previously mentioned system tables/views, there is a replacement, as shown in Table 3.2.

It is possible to write a database management application that uses some of the information from these tables. The information from these tables is more reliable than what can be found in other resources. Many of the columns in system tables are not documented, and you should only apply those whose supporting documentation is known, so you need to be sure to refer to the documentation. You should not write applications to directly query undocumented columns.

Instead of trying to retrieve information stored in system tables, you can create applications that access the information via system stored procedures, T-SQL statements and functions, SQL Server Management Objects (SMO), Replication Management Objects (RMO), or Database API catalog functions. These components make up a published API for obtaining system information from SQL Server.

Microsoft maintains compatibility of these alternative components from version to version. The format of the system tables depends on the internal architecture of SQL Server and may change from release to release. The supporting procedure and functions, however, still accommodate the required information. Applications that directly access the undocumented columns of system tables may have to be changed in a future release.

Information Retrieval from System Stored Procedures

Many stored procedures can provide information about the state of objects. The following are some of the most common procedures to gain this information from the database engine:

• sp_help — Provides a list of objects if no parameters are supplied. If you supply an object name or ID, it provides information about the object.
• sp_help objecttype or sp_help_ objectytpe — Provides information about a specified type of object. You can replace objecttype with just about any SQL Server object.
• sp_table_validation — Provides checksum or row count for a table. If you provide the checksum or row count, the procedure will validate the table against the supplied value.
• sp_settriggerorder — Specifies the first or last trigger to fire if multiple triggers have been defined. The order of other triggers between the first and the last cannot be set or guaranteed.
• sp_lock — Reports information about current locks that are in place.
• sp_configure — Displays or alters server configuration settings.
• sp_who — Provides information about a user's current logins, sessions, and processes.
• sp_updatestats — Updates the statistics for every table in the current database.

Keep in mind that there are somewhere in the neighborhood of 1,500 stored procedures that can be used within SQL Server. Because this chapter cannot cover every one of them, the proceeding list contains the ones you are most likely to see on the 70-431 exam and/or use on a regular basis as a database administrator.

Using Dynamic Management Views and Other System Views

SQL Server provides many dynamic management views and functions. These views and functions return server state information. The best use for these views and functions is in monitoring and determining the health of a server instance. They can be useful in diagnosing problem situations and providing information to assist in performance tuning.

All dynamic management views and functions exist in the sys schema. The naming for these views and functions follows the dm_* convention. Each database contains almost 100 different dynamic management views and functions. Because of the naming convention, you can find categories of the dynamic management views by using a query of sysobjects similar to the following:

SELECT * FROM sysobjects
    WHERE NAME LIKE 'dm_db%'
SELECT * FROM sysobjects
    WHERE NAME LIKE 'dm_db_index%'
SELECT * FROM sysobjects
    WHERE NAME LIKE 'dm_fts%'

Some of the most important categories and their common views and functions are detailed in the following list:

• dm_db_index_operational_stats — Reports current locking and access, by partition.
• dm_db_index_usage_stats — Specifies a count of index operations and times of last occurrences.
• dm_db_index_physical_stats — Provides index fragmentation information.
• dm_fts_index_population — Displays status information on the index population.
• dm_fts_active catalogs — Reports any population activity in progress.
• dm_fts_populations_ranges —Specifies the memory address ranges in use.
• dm_exec_query_stats — Specifies performance statistics for cached query plans.
• dm_exec_query_plan — Displays the XML show plan for the cached query plan.
• dm_exec_cached_plans — Specifies the currently cached execution plans held by the server.

The first time you look, the views and the results returned by the functions look peculiar at best. However, with time and experience, the views will appear normal.

Inserting Data

You must be able to accurately get data into a system, and it needs to be organized for efficient retrieval. Although initial data loading is performed by other means as time passes, new data will no doubt need to be inserted individually or in small groups of records.

Although there are many ways to insert data into an existing table, the primary coding method is by using the INSERT statement. This statement causes the data values to be inserted into an existing table as one or more rows.

Data must meet all rules and constraints that have been defined in the table schema. The type of data being inserted must be suitable input for the data types within the table definition. Data types can themselves have control mechanisms or, with the advent of common language runtime (CLR) user-defined types (UDTs), can be complete processes for checking and manipulating data as it enters the system.

Using UDT and the CLR to Control Data Input

UDTs and the CLR component offer functionality not previously available in SQL Server. In previous releases, UDTs were only a mapping of existing data types that you could place rules and defaults onto, but little else.

The functionality of CLR UDTs is powerful but should not be overused. There is a significant amount of overhead associated with CLR use. The CLR is disabled by default in a new SQL Server installation, and to use it to its full extent, you must write the .DLL assembly in a format defined by UDT standards.

Using the CLR Within Stored Procedures

In many respects, the CLR is a wonderful thing. It opens up the opportunity to use other programming languages, such as C#, Visual C++, Visual Basic, and others, to create procedures executed within the database engine. The CLR is implemented through the use of assemblies that are referred to as managed code. Managed code is executed in the CLR environment rather than directly by the operating system.

The ability to use CLR objects within SQL Server is disabled by default upon SQL Server installation; this is partially because of the overhead involved in its use. The CLR need not be enabled in the majority of database environments. The deployment of CLR is not likely to achieve widespread use, at least initially.

The CLR will be more efficient than T-SQL in many instances. Managed code will outperform T-SQL in situations where there is use of procedural code, computation, and string manipulation. The CLR will perform better than T-SQL in any process that is computing intensive. The CLR should not be used to perform data access; T-SQL, which is specifically designed for interaction with the database engine, performs data access more efficiently than the CLR.

Not all calculations perform better in a CLR environment than with T-SQL. Managed code is moderately slower than built-in SQL Server aggregate functions, but it outperforms any cursor-based aggregation.

Inserting Individual Records

Data inserted must meet the parameters defined by the table structure. This means that NOT NULL columns must have data provided either through input or through the use of column definitions that provide for their own values. A column can obtain its input value through a DEFAULT, IDENTITY, formula, timestamp, or default object.

When inserting data, you specify the VALUES keyword to supply the data. VALUES is required unless you are using INSERT/SELECT, SELECT INTO, or EXECUTE. The following example shows the addition of a single record, using VALUES for each field:

INSERT INTO Customers
      VALUES('H99999', 'Jillier and Jergenson', 'Special',
                 'John Smith', NULL, NULL, '123 Mill Street',
                  NULL, NULL, 'US', 'Miami', 'FL', '27622',
                  '292-782-6378', NULL, NULL, NULL, 0, NULL)

Many of these fields have no value supplied, and it might be easier and neater to provide a field list with INSERT, as follows:

INSERT INTO Customers
              (CUSTNMBR, CUSTNAME, CUSTCLAS,
               CNTCPRSN, ADDRESS1, COUNTRY,
               CITY, STATE, ZIP, PHONE1, INACTIVE)
     VALUES('H99999', 'Jillier and Jergenson', 'Special',
                'John Smith', '123 Mill Street', 'US',
                'Miami', 'FL', '27622', '292-782-6378', 0)

These two statements have the same result when adding a single record to the table.

Using a Query to Insert Complete Recordsets

The SELECT INTO statement can perform a data insertion and create the table for the data in a single operation. The new table is populated with the data provided by a FROM clause. The SELECT INTO statement creates a new table with a structure identical to that of the columns provided in the query. It then copies all data that meets the WHERE condition into this newly created table. It is possible to combine data from several tables or views into one table, and you can use a variety of sources. The following example creates a new table within a database that contains only two columns:

SELECT FirstName + ' ' + LastName
     AS 'Employee Name',
                Title
     INTO HRTable
     FROM Employees

The INTO clause creates a table, so it is important that the table does not exist when you're using the command. If you want to add data to an existing table, you must perform an INSERT INTO operation. You can use a SELECT statement within the INSERT statement to add values to a table from one or more other tables or views. Using a SELECT subquery is another mechanism that enables more than one row to be inserted at one time. This type of INSERT statement is often used to insert data into a separate table from some other table or data source. In this manner, the data can be copied or just separated for handling of exceptions or specialty tasks.

For example, imagine that you would like to copy all your current employees into a customer table to enable them to make purchases and, of course, allow for an employee discount. The query to perform this operation might look similar to the following:

INSERT INTO Customers
       SELECT EmployeeID, 'TOMORA Systems',
              'Employee', FirstName + ' ' + LastName,
              'N/A', 'INTERNAL', Address, NULL, NULL,
              Country, City, Region, PostalCode,
               HomePhone, NULL, NULL, NULL, 0, NULL
          FROM Employees

The SELECT list of the subquery must match the column list of the INSERT statement. If no column list is specified, the SELECT list must match the columns in the table or view being inserted into, as in the example. Note that NULL has been provided for several fields as a placeholder for columns in which there is no data.

You can use the INSERT SELECT statement to insert data from any viable source. This includes SQL Server tables and views, as well as sources outside SQL Server. Often, the operation is used in more involved procedures to move data to and from temporary tables or table variables.

Temporary tables exist only during the duration of the procedure, so they need to be loaded during the process and offloaded before the procedure ends. A temporary table is defined using the # prefix for local temporary tables that are accessible only to the immediate scope/batch or ## for global temporary tables that are accessible outside the current batch. These are used less often because SQL Server now has a table data type that can be used for this purpose.

Disabling Functionality During Data Insertion

At times, you might want to disable indexes, triggers, constraints, and other objects to improve performance and prevent errors from occurring while loading data. This is particularly useful when loading large amounts of data in a bulk or batch format.

Disabling Indexes

Disabling an index puts the index to sleep and prevents the system from accessing it until it is enabled again. The index definition remains in the system catalog. To see the status of an index, you can query the is_disabled column in the sys.indexes catalog view. The DISABLE INDEX feature is new to SQL Server 2005 and is therefore very likely to be on the exam.

It really makes sense to disable only nonclustered indexes. Disabling a clustered index prevents access to the data. The data remains in the B-tree and must be dropped or rebuilt to correct the situation. Disabling an index on a view physically deletes the data associated with the index.

If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.

You use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement to enable an index. You cannot rebuild a disabled clustered index when the ONLINE option is set to ON. For more information, see the information on DISABLE INDEX in SQL Server Books Online.

Disabling Trigger Firing

Disabling a trigger does not drop the trigger. The definition of the trigger still exists as an object in the current database. A disabled trigger does not fire when any T-SQL statements on which it was programmed are executeds. This applies to both DDL and DML triggers.

To disable a trigger, you simply use the DISABLE TRIGGER statement. To turn it back on again, you use ENABLE TRIGGER.

Disabling Constraint Checking

You can use ALTER TABLE with the NOCHECK CONSTRAINT clause to disable a constraint. While a constraint is disabled, the system allows for an insertion of data that would typically violate the constraint. You might want this in rare situations, but it would corrupt most systems.

Using ALTER TABLE with CHECK CONSTRAINT turns the constraint back on again.