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.

Tip

If you are just starting out with T-SQL, you might find it helpful to look at what is already in the system. If you select any of the system views or stored procedures and then select the modify option, you can see the code behind the process. You can learn a lot when you look at how these views and processes are put together.

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

Current View/Table Name	Old View/Table Name	Description
Common System Views/Tables in Every Database (Including master):
sys.columns	syscolumns	Contains a row for every column in every table, for every view, and for each parameter in a stored procedure.
sys.indexes, sys.partitions, sys.allocation_units, and sys.dm_db_partition statssys	sysindexes	Contains a row for each index and table in the database.
sys.sql_modules	syscomments	Contains entries for each view, rule, default, trigger, constraint, and stored procedure. The text column contains the original SQL definition statements.
sys.objects	sysobjects	Contains a row for each object created within a database. In tempdb only, this table includes a row for each temporary object.
sys.types	systypes	Contains a row for each system-supplied data type and each user-defined data type.
sys.database_principals	sysusers	Contains a row for each user or role in the database.
Common System Views/Tables Additionally Found in the master Database Only:
sys.server_principals	syslogins	Contains a row for each login.
sys.messages	sysmessages	Contains a row for each system error or warning that can be displayed to the user.
sys.databases	sysdatabases	Contains a row for each database on the server.
sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests	sysprocesses	Holds information about processes running on the server.
sys.remote_logins	sysremotelogins	Contains a row for each remote user allowed to call remote stored procedures on the server.
sys.servers	sysservers	Contains a row for each server that the current server can access as an OLE DB data source.

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.

Exam Alert

The information provided in this section is the official Microsoft stance. If you see exam questions asking you where to access reliable information and apply that to applications that will be supported in the future, you should select system stored procedures, T-SQL statements and functions, SMO, RMO, or Database API catalog functions.

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_helpobjecttype 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.

Exam Alert

There are a lot of commonly used stored procedures. You can expect some of them to be correct answers on 70-431 exam questions. Others might be distracters or red herrings. When you see sp_ as one of the answers, you should read the question carefully and understand what each procedure will and will not provide before selecting it as the answer.

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.

Exam Alert

All dynamic management views are specific to the version of SQL Server. There is no guarantee that the current views will be supported in future releases. Therefore, you should find the equivalent stored procedures to get the desired information for something that will be in prolonged use.

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.

Exam Alert

The CLR allows you to encapsulate business logic within a data type itself. It is possible to write assemblies that contain the processing to represent, persist, and manipulate complex numbers.

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.

Exam Alert

You should use CLR procedures and managed code for processes that involve complex calculations but do not perform standard data interactions. You should use T-SQL procedures for processes that are standard database activities with minimal complex calculations.

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.

Exam Alert

Watch for column definitions that are NOT NULL. These fields must get data during an insert. This data can be supplied either through a value or via IDENTITY, DEFAULT, a computed column, or a timestamp.

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.