Microsoft SQL Server 7.0 System Administration Training Kit
An understanding of SQL Server database structure will help you develop and implement your database effectively. This lesson discusses the types of databases found in SQL Server and also describes two types of structural elements: database objects and system tables.
After this lesson, you will be able to
- Describe SQL Server system and user databases
- Understand how database objects are named and referenced in SQL Server
- Describe the purpose of SQL Server system tables
- Define metadata and know how to retrieve it
Estimated lesson time: 30 minutes
Types of Databases
Each SQL Server has two types of databases: system databases and user databases. Structurally there is no difference between system and user databases, both types of databases store data. However, SQL Server recognizes and requires system databases for its own use. System databases store information about SQL Server as a whole. SQL Server uses these databases to operate and manage the system. User databases are databases that users create. One copy of SQL Server can manage one or more user databases. When SQL Server is installed, SQL Server Setup creates four system databases and two sample user databases.
System Databases
The following table describes the system databases.
| Database | Description |
|---|---|
| master | Controls the user databases and operation of SQL Server as a whole by keeping track of information such as login accounts, configurable environment variables, database locations, and system error messages |
| model | Provides a template, or prototype, for new user databases |
| tempdb | Provides a storage area for temporary tables and other temporary working storage needs |
| msdb | Supports SQL Server Agent and provides a storage area for scheduling information and job history |
| distribution | Stores history and transaction data used in replication |
NOTE
The distribution database is installed only when you configure SQL Server for replication activities.
While it is possible to modify and delete data in the system databases, this is not recommended. You should create all user objects in user databases and use system stored procedures only to read and modify data in the system databases.
There is one case in which you can modify a system database directly. If you want certain objects that you create (such as stored procedures, data types, defaults, and rules) to be added to every new user database, you can add these objects to the model database. The contents of the model database are copied into every new database.
User Databases
The pubs and Northwind sample databases are installed when you install SQL Server. These provide useful examples for you to use when learning how to work with SQL Server. They are not required for SQL Server to operate correctly.
Database Objects
A database is a collection of data stored in tables, along with objects that support the storage, retrieval, security, and integrity of this data.
The following table summarizes the SQL Server database objects.
| Database object | Description |
|---|---|
| Table | Stores data as a collection of rows and columns. |
| Data type | Defines the type of data values allowed for a column or variable. SQL Server provides system-supplied data types. Users can create user-defined data types. |
| Constraint | Used to define integrity rules for a column or set of columns in a table; the standard mechanism for enforcing data integrity. |
| Default | Defines a value that is stored in a column if no other value is supplied. |
| Rule | Defines an expression that is used to check the validity of values that are stored in a column or data type. |
| Index | A storage structure that provides ordering and fast access for data retrieval and that can enforce data uniqueness. |
| View | Provides a way to look at data from one or more tables or other views in a database. |
| Stored procedure | A named collection of Transact-SQL statements or batches that execute together. |
| Trigger | A special form of a stored procedure that is executed automatically when a user modifies data in a table. |
NOTE
In Enterprise Manager, system databases and system objects are hidden by default. You can change the default by editing the server registration information and checking the Show System Databases And System Objects option.
Referring to SQL Server Objects
You can refer to SQL Server objects in several ways. You can specify the full name of the object (its fully qualified name), or you can specify only part of the object's name and have SQL Server determine the rest of the name from the context in which you are working.
Fully Qualified Names
The complete name of a SQL Server object includes four identifiers: the server name, the database name, the owner name, and the object name, in the following format:
| server.database.owner.object |
Any name that specifies all four parts is known as a fully qualified name. Each object created in SQL Server must have a unique, fully qualified name. For example, there can be two tables named Orders in the same database only as long as they belong to different owners. In addition, column names must be unique within a table or view.
Partially Specified Names
When referencing an object, you do not always have to specify the server, database, and owner. Leading identifiers can be omitted. Intermediate identifiers can also be omitted as long as their position is indicated by periods. The valid formats of object names are as follows:
| server.database.owner.object server.database..object server..owner.object server...object database.owner.object database..object owner.object object |
When you create an object, SQL Server uses the following defaults if different parts of the name are not specified:
- The server defaults to the local server.
- The database defaults to the current database.
- The owner defaults to the username in the specified database associated with the login ID of the current connection. (Usernames are mapped to login IDs when they are created.)
A user who is a member of a role can explicitly specify the role as the object owner. A user who is a member of the db_owner or db_ddladmin role in the Northwind database can specify the dbo user account as the owner of an object. This practice is recommended.
The following example creates an order_history table in the Northwind database.
| CREATE TABLE northwind.dbo.order_history ( OrderID INT , ProductID int , UnitPrice money , Quantity int , Discount decimal ) |
Most object references use three-part names and default to the local server. Four-part names are generally used for distributed queries or remote stored procedure calls.
System Tables
System tables store information, called metadata, about the system and objects in databases. Metadata is information about data.
The Database Catalog
Each database (including the master database) contains a collection of system tables that store metadata about that specific database. This collection of system tables is called the database catalog.
The System Catalog
The system catalog, found only in the master database, is a collection of system tables that stores metadata about the entire system and all other databases.
System tables all begin with the sys prefix. The following table identifies some frequently used system tables.
| System table | Database | Function |
|---|---|---|
| sysxlogins | master | Contains one row for each login account that can connect to SQL Server. If you need to access information in sysxlogins, you should do so through the syslogins view. |
| sysmessages | master | Contains one row for each system error or warning that SQL Server can return. |
| sysdatabases | master | Contains one row for each database on a SQL Server. |
| sysusers | All | Contains one row for each Windows NT user, Windows NT group, SQL Server user, or SQL Server role in a database. |
| sysobjects | All | Contains one row for each object in a database. |
Metadata Retrieval
You can query a system table as you would any other table to retrieve information about the system. However, you should not write scripts that directly query the system tables, because if the system tables are changed in future product versions, your scripts may fail or may not provide accurate information.
CAUTION
Writing scripts that directly modify the system tables is strongly discouraged. Changing a system table may make it impossible for SQL Server to operate normally.
When you write applications that retrieve metadata from system tables, you should use system stored procedures, system functions, or system-supplied information schema views. Each of these is described in the sections that follow.
System Stored Procedures
To make it easier for you to gather information about the state of the server and database objects, SQL Server provides a collection of prewritten queries called system stored procedures.
The names of most system stored procedures begin with the sp_ prefix. The following table describes three commonly used system stored procedures.
| System stored procedure | Description |
|---|---|
| sp_help [object_name] | Provides information on the specified database object |
| sp_helpdb [database_name] | Provides information on the specified database |
| sp_helpindex [table_name] | Provides information on the index for the specified table |
The following example executes a system stored procedure to get information on the employee table.
| sp_help employee |
Many other stored procedures are used to create or modify system information or database objects by modifying the system tables. For example, the system stored procedure sp_addlogin creates a new login account in the master..sysxlogins system table.
As you have seen, there are system stored procedures that modify and query the system tables for you so that you do not have to do so directly.
System Functions
System functions provide a method for querying system tables from within Transact-SQL statements. System functions return specific, single values. The following table describes commonly used system functions and the information they return.
| System function | Parameter passed | Results |
|---|---|---|
| DB_ID | Database name | Returns the database ID |
| USER_NAME | User ID | Returns the user's name |
| COL_LENGTH | Table and column names | Returns the column width |
| STATS_DATE | Table and index IDs | Returns the date when statistics for the specified index were last updated |
| DATALENGTH | Expression | Returns the actual length of the value of an expression of any data type |
The following example uses a system function in a query to get the user name for user ID 10.
| SELECT USER_NAME(10) |
Information Schema Views
Information schema views provide an internal, system table_independent view of the SQL Server metadata. These views conform to the ANSI SQL standard definition for information schema. Information schema views allow applications to work properly even if future product versions change the system tables significantly.
In SQL Server, all information schema views are owned by a predefined information_schema user.
Each information schema view contains metadata for the data objects stored in a particular database. The following table describes commonly used information schema views.
| Information schema view | Description |
|---|---|
| information_schema.tables | List of tables in the database |
| information_schema.columns | Information on columns defined in the database |
| information_schema.tables_privileges | Security information for tables in the database |
The following example queries an information schema view to get a list of tables in a database.
| SELECT * FROM information_schema.tables |
Lesson Summary
The retrieval of metadata—information about objects and their configuration—has been made much easier in SQL Server 7. Information Schema Views, new to this version, provide a means to retrieve valuable information from system tables without writing a query against these tables yourself. SQL Server continues to support the use of system stored procedures, which can be recognized by their sp_ prefix, to gather valuable information for database objects.