[Top] [Prev] [Next]

Chapter 7

Database Objects and the Data Dictionary

Overview

SQL Server's active data dictionary centralizes the storage of database definitions for all database applications. Definitions of database objects such as procedures, rules, triggers, constraints and defaults need be updated only once in the data dictionary, simplifying application development and maintenance. This chapter introduces these objects and the data dictionary while discussing the following topics:

System Databases

When you install SQL Server, it includes these system databases:

If you choose to install auditing, your SQL Server will also have a security database. In addition, you can optionally install the sample database, pubs2, and the syntax database, sybsyntax.

Master Database

The master database controls the operation of SQL Server as a whole and stores information about all user databases and their associated database devices. It keeps track of:

Model Database

The model database provides a template for new user databases. Each time you issue the create database command to create a new user database, SQL Server makes a copy of the model database and then extends it to the size you request in the create database command.

The model database contains the system tables required for each user database. You can modify it to customize the structure of newly created databases¯everything you do to model is reflected in each new user database.

Some of the changes that System Administrators commonly make to model are:

System Procedures Database

 The system procedures database, sybsystemprocs, stores Sybase system procedures, which provide information about system tables and help you with system administration. You can identify system stored procedures by their "sp_" prefix. The contents of the sybsystemprocs database are available to any other database on SQL Server.

Temporary Database

SQL Server supplies a temporary database, tempdb, that serves as a storage area for temporary tables and other temporary storage needs. All databases on SQL Server share tempdb.

Security Database

You can choose to activate SQL Server's auditing function to record security-related system activity in an audit trail. Events are first recorded in an audit queue, then saved to the security database.

The security database, sybsecurity, contains the audit system for SQL Server. It consists of two tables in addition to the regular system tables:

Sample Database

 Installing the sample database, pubs2, is optional. Provided as a learning tool, pubs2 is the basis of most examples in the SQL Server document set. It is a complete database with many tables and various associated objects. It is useful to have a ready-made practice database so you can try out commands and procedures without worrying about mistakenly damaging important production data.

Syntax Database

The syntax database (sybsyntax) provides syntax help for Transact-SQL commands, system stored procedures, Sybase utility programs, and Open Client language modules. The system procedure sp_syntax retrieves information from this database. Installing this database is optional.

System Tables

The master Database contains system tables that hold information to manage the SQL Server system; all databases (system and user databases) contain system tables that hold information to manage events and objects within that database.

All tables in the master database are system tables. If you create a new user database, SQL Server automatically establishes some system tables in your new database as well to keep track of database-specific activities. Table 7-1, Table 7-2, and Table 7-3 list all of SQL Server's system tables, where each resides, and what each contains.

Table 7-1: System tables that occur in all databases

System Table Contents
sysalternates One row for each SQL Server user mapped to a database user
sysattributes One row for each object attribute definition.
syscolumns One row for each column in a table or view, and for each parameter in a procedure
syscomments One or more rows for each view, rule, default, trigger, and procedure giving SQL definition statement
sysconstraints One row for each referential and check constraint associated with a table or column
sysdepends One row for each procedure, view, or table that is referenced by a procedure, view, or trigger
sysindexes One row for each clustered or nonclustered index, one row for each table with no indexes, and an additional row for each table containing text or image data
syskeys One row for each primary, foreign, or common key; set by user (not maintained by SQL Server)
syslogs Transaction log
sysobjects One row for each table, view, procedure, rule, trigger default, log, and (in tempdb only) temporary object
syspartitions One row for each partition (page chain) of a partitioned table
sysprocedures One row for each view, rule, default, trigger, and procedure giving internal definition
sysprotects User permissions information
sysreferences One row for each referential integrity constraint declared on a table or column
sysroles Maps server-wide roles to local database groups
syssegments One row for each segment (named collection of disk pieces)
systhresholds One row for each threshold defined for the database
systypes One row for each system-supplied and user-defined datatype
sysusermessages One row for each user-defined message
sysusers One row for each user allowed in the database

The following system tables occur in the master database only:

Table 7-2: System tables that occur in the master database only

System Table Contents
syscharsets One row for each character set or sort order
sysconfigures One row for each user-settable configuration parameter
syscurconfigs Information about configuration parameters currently being used by SQL Server
sysdatabases One row for each database on SQL Server
sysdevices One row for each tape dump device, disk dump device, disk for databases, and disk partition for databases
sysengines One row for each SQL Server engine currently online
syslanguages One row for each language (except U.S. English) known to the server
syslisteners One row for each type of network connection used by current SQL Server
syslocks Information about active locks
sysloginroles One row for each server login that possesses a system-defined role
syslogins One row for each valid SQL Server user account
syslogshold Information about the oldest active transaction and the Replication Server truncation point for each database
sysmessages One row for each system error or warning
sysprocesses Information about server processes
sysremotelogins One row for each remote user
syssrvroles One row for each server-wide role
sysservers One row for each remote SQL Server
sysusages One row for each disk piece allocated to a database

The following system tables occur in the sybsecurity database only:

Table 7-3: System tables that occur in the sybsecurity database only

System Table Contents
sysauditoptions One row for each global audit option

User Databases and Database Objects

User databases are created with the create database command. All new databases must be created from inside the master Database. No user without access to the master database and explicit permission can create user databases.

A new database initially contains a set of system tables with entries that describe the system tables themselves. The sysusers table contains at least one entry¯the user name of the database creator. By default, the database creator is also its owner, but the database creator can transfer ownership to another user.

The database owner is granted many privileges and responsibilities for controlling the system tables and the rest of the database.

Database Objects

The SQL Server database stores data in different types of database objects created by users with the proper permissions. Objects are Transact-SQL constructs that take up space in the database. SQL Server database objects include:

Figure 7-1 illustrates a user database and its objects.

Figure 7-1: Database and objects

Except for tables and indexes, all of these objects are discussed in Chapter 5, "Transact-SQL and Utilities" as Transact-SQL extensions to SQL. Triggers, rules, defaults, and check constraints are used for enforcing data integrity and are discussed in more detail in Chapter 8, "Data Integrity and Consistency."

Tables

Tables represent data in a column-and-row structure that is the hallmark of the relational model of database management. When you create a table, you name its columns and assign each one a datatype. You can optionally include other integrity constraints on columns or combinations of columns.

Indexes

The performance of most relational database management systems degrades considerably as the number of rows in a table increases. Benchmarks have demonstrated, however, that SQL Server shows very little difference in either throughput or response time between a 100,000-row table and a 1,000,000-row table.

SQL Server accomplishes this using a sophisticated form of B-tree indexing known as a clustered index. Clustered indexes allow data to be stored in the leaf level of the index and frequently eliminate I/O in retrieving data. The clustered index stores both the data and its index in a sorted order.

The power of this form of indexing is illustrated by the fact that even with a 1,000,000-row table, the average number of writes necessary to perform an update and a commit¯including logging¯is .95. This compares very favorably with an average of .84 writes for the same transaction with a 100,000-row table, and explains why SQL Server shows little degradation as the number of rows in a table increases.

Up to 249 additional indexes per table can be specified as nonclustered, where the leaf level of the index consists of pointers to the data pages. Composite indexes, which combine the values in more than one column into a single key, can be created on up to 16 columns with a maximum key size of 256 bytes.

An index can be defined as unique, with no two rows having the same key value. The create index command includes several other options for handling duplicate index values and rows, and for fine-tuning performance.

System Datatypes

A column's datatype specifies the kind of information (characters, numbers, dates) that the column holds, and how the data is stored. SQL Server supplies a wide variety of datatypes, including text for long entries of printable characters and image for long entries of binary data. Table lists SQL Server's datatypes.

Table 7-4: SQL Server system datatypes

Datatypes by Category Synonyms Range Bytes of Storage
Exact numeric: integers
tinyint NA 0 to 255 1
smallint NA 215 -1 (32,767) to -215 (-32,768 2
int integer 231 -1 (2,147,483,647) to -231 (-2,147,483,648) 4
Exact numeric: decimals
numeric (p, s) NA 1038 -1 to -1038 2 to 17
decimal (p, s) dec 1038 -1 to -1038 2 to 17
Approximate numeric
float (precision) NA machine dependent 4 or 8
double precision NA machine dependent 8
real NA machine dependent 4
Money
smallmoney NA 214,748.3647 to -214,748.3648 4
money NA 922,337,203,685,477.5807 to-922,337,203,685,477.5808 8
Date/time
smalldatetime NA January 1, 1900 to June 6, 2079 4
datetime NA January 1, 1753 to December 31, 9999 8
Character
char(n) character 255 characters or less n
varchar(n) character varying, char varying 255 characters or less actual entry length
nchar(n) national character, national char 255 characters or less n * @@ncharsize
nvarchar(n) nchar varying, national char varying, national character varying 255 characters or less @@ncharsize * number of characters
text NA 231 -1 (2,147,483,647) bytes or less 0 or multiple of 2K
Binary
binary(n) NA 255 bytes or less n
varbinary(n) NA 255 bytes or less actual entry length
image NA 231 -1 (2,147,483,647) bytes or less 0 or multiple of 2K
sensitivity_boundary NA Data low to data high 4
Bit NA
bit NA 0 or 1 1 (1 byte holds up to 8 bit columns)

Null Values

Every Transact-SQL datatype specification, other than that for "bit", can include a specification of null or not null. If a column is defined as not null, no user can insert a null value into the column. Columns with the null specification allow null values.

User-defined Datatypes

Users can name and define their own datatypes to supplement the system-supplied datatypes. User datatypes, a Transact-SQL enhancement of SQL, are defined in terms of system datatypes. For example, every column that contains some kind of information about names might be assigned the user datatype nm, which might be defined as varchar(50), with a rule and default attached to it.

User datatypes permit custom data definition, provide an additional measure of consistency across applications, and can save keystrokes and prevent errors in data definition.

System Procedures

System procedures are stored procedures that provide shortcuts for retrieving information from the system tables, mechanisms for accomplishing database administration, and other tasks that involve updating system tables. A separate Sybase product, SQL Companion, provides a point-and-click interface for performing these tasks.

the system procedures are located in the sybsystemprocs database and are owned by the System Administrator. A System Administrator or other user with permission can create stored procedures in sybsystemprocs that can be executed from any database on the server. Table 7-5 through Table 7-17 summarize the stored procedures.


Table 7-5: System procedures for managing constraints, rules and defaults
Procedure Description
sp_bindefault Binds a default to a column or user-defined datatype.
sp_bindmsg Binds a user message to a referential integrity constraint or check constraint.
sp_bindrule Binds a rule to a column or user-defined datatype.
sp_helpconstraint Reports information about integrity constraints on a table.
sp_helptext Prints the text of a system procedure, trigger, view, default, rule, or integrity check constraint.
sp_unbindefault Unbinds a created default value from a column or from a user-defined datatype.
sp_unbindmsg Unbinds a user-defined message from a constraint.
sp_unbindrule Unbinds a rule from a column or from a user-defined datatype.


Table 7-6: System procedures for managing database objects
Procedure Description
sp_commonkey Defines a common key¯columns that are frequently joined¯between two tables or views.
sp_chgattribute Changes the max_rows_per_page value for future space allocations of a table or index.
sp_depends Displays information about database object dependencies¯the view(s), trigger(s), and procedure(s) that are dependent on the table or view specified, and the table(s) and view(s) that are dependent on the view, trigger, or procedure specified.
sp_dropkey Removes from the syskeys table a key that had been defined using sp_primarykey, sp_foreignkey, or sp_commonkey.
sp_estspace Estimates the amount of space required for a table and its indexes, and the time needed to create the index.
sp_foreignkey Defines a foreign key on a table or view in the current database.
sp_help Reports information about a database object (any object listed in sysobjects), and about SQL Server-supplied or user-defined datatypes.
sp_helpartition Lists the first page and control page for each partition of a partitioned table.
sp_helpindex Reports information about the indexes created on a table.
sp_helpjoins Lists the columns in two tables or views that are likely join candidates.
sp_helpkey Reports information about a primary, foreign, or common key of a particular table or view, or about all keys in the current database.
sp_helprotect Reports on permissions for database objects, users, or groups.
sp_primarykey Defines a primary key on a table or view.
sp_procqmode Reports the subquery processing mode of an object.
sp_rename Changes the name of a user-created object in the current database.
sp_spaceused Displays the number of rows, the number of data pages, and the space used by one table or by all tables in the current database.


Table 7-7: System procedures for managing databases
Procedure Description
sp_changedbowner Changes the owner of a database.
sp_dboption Displays or changes database options.
sp_dbremap Forces SQL Server to recognize changes made by alter database.
sp_helpdb Reports information about a particular database, or about all databases.
sp_renamedb Changes the name of a database.


Table 7-8: System procedures for managing user caches
Procedure Description
sp_bindcache Binds databases, tables, indexes, or text or image chains to data caches.
sp_cacheconfig Configures named data caches and provides information about caches.
sp_cachestrategy Enables and disables caching strategies and large I/O for specific tables and indexes.
sp_helpcache Provides information about cache overhead requirements and about caches and cache bindings.
sp_unbindcache Unbinds a specific database, table, index, or text or image chain from a data cache.
sp_unbindcache_all Unbinds all objects bound to a cache.


Table 7-9: System procedures for managing devices and storage
Procedure Description
sp_addsegment Defines a segment on a database device in the current database.
sp_addthreshold Creates a free-space threshold to monitor the space remaining on a database segment. When free space on the segment falls below the specified level, SQL Server executes the associated stored procedure.
sp_addumpdevice Adds a dump device to SQL Server.
sp_diskdefault Sets a database device's status to defaulton or defaultoff to indicate whether a database device is to be used for default storage.
sp_dropdevice Drops a SQL Server database device or dump device.
sp_dropsegment Drops a segment from a database or unmaps a segment from a particular database device.
sp_dropthreshold Removes a free-space threshold from a segment.
sp_extendsegment Extends the range of a segment to another database device, or extends the current segment on the current database device.
sp_helpdevice Reports information about a particular device, or about all SQL Server database devices and dump devices.
sp_helplog Reports the name of the device that contains the first page of the log.
sp_helpsegment Reports information about a particular segment or about all of the segments in the current database.
sp_helpthreshold Reports information about all thresholds in the current database or all thresholds for a particular segment.
sp_logdevice Puts the system table syslogs, which contains the transaction log, on a separate database device.
sp_logiosize Changes the log I/O size used by SQL Server to a different memory pool when doing I/O for the transaction log of the current database.
sp_modifythreshold Modifies a threshold by associating it with a different threshold procedure, level of free space, or segment.
sp_placeobject Puts future space allocations for a table or index on a particular segment.
sp_poolconfig Configures pools within named caches to enable large I/O.
sp_spaceused Displays the number of rows, the number of data pages, and the space used by one table or by all tables in the current database.
sp_thresholdaction Default threshold procedure called when the log segment of a database reaches the last-chance threshold. This procedure is not supplied; it must be created by the user.


Table 7-10: System procedures for managing remote servers and remote logins
Procedure Description
sp_addremotelogin Authorizes a new remote server user.
sp_addserver Defines a remote server, or defines the name of the local server.
sp_dropremotelogin Drops a remote user login.
sp_dropserver Drops a server from the list of known servers.
sp_helpremotelogin Reports information about a particular remote server's logins, or about all remote servers' logins.
sp_helpserver Reports information about a particular remote server or about all remote servers.
sp_remoteoption Displays or changes remote login options.
sp_serveroption Displays or changes remote server options.


Table 7-11: System procedures for managing server operations
Procedure Description
sp_configure Displays or changes configuration variables.
sp_cursorinfo Reports information about a specific cursor or all cursors that are active.
sp_displaylevel Sets and displays a user's display level. The display level determines which SQL Server configuration parameters are displayed in sp_configure output.
sp_dropglockpromote Removes lock promotion values from a table or database.
sp_lock Reports information about processes that currently hold locks.
sp_monitor Displays statistics about SQL Server.
sp_setpglockpromote Sets or changes the lock promotion thresholds for a database, table, or for SQL Server.
sp_volchanged Notifies the Backup Server that the operator has performed the requested volume handling during a dump or load.
sp_who Reports information about a particular user or about all current SQL Server users and processes.


Table 7-12: System procedures for managing stored procedures
Procedure Description
sp_helptext Prints the text of a system procedure, trigger, view, default, rule, or integrity check constraint.
sp_procxmode Displays or changes the transaction modes associated with stored procedures.
sp_recompile Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.


Table 7-13: System procedures for managing upgrades
Procedure Description
sp_checkreswords Detects and displays identifiers that are Transact-SQL reserved words. Checks server names, device names, database names, segment names, user-defined datatypes, object names, column names, user names, login names, and remote login names.
sp_remap Remaps a release 4.8 or later stored procedure, trigger, rule, default, or view to be compatible with release 10.0, if upgrade could not remap it.


Table 7-14: System procedures for managing user-defined datatypes
Procedure Description
sp_addtype Creates a user-defined datatype.
sp_droptype Drops a user-defined datatype.
sp_help Reports information about user-defined datatypes.


Table 7-15: System procedures for managing user-defined messages
Procedure Description
sp_addmessage Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls, and sp_bindmsg.
sp_dropmessage Drops user-defined messages from sysusermessages.
sp_getmessage Retrieves stored message strings from sysmessages and sysusermessages for print and raiserror statements.


Table 7-16: System procedures for managing users and logins
Procedure Description
sp_addalias Maps one user to another in a database.
sp_addgroup Adds a group to a database.
sp_addlogin Adds a new user account to SQL Server.
sp_adduser Adds a new user to the current database.
sp_changegroup Changes a user's group.
sp_clearstats Initiates a new accounting period for all server users, or for a specified user. Prints statistics for the previous period by executing sp_reportstats.
sp_displaylogin Displays information about a login account.
sp_dropalias Removes the alias user name identity that had been established with sp_addalias.
sp_dropgroup Drops a group from a database.
sp_droplogin Drops a SQL Server user account.
sp_dropuser Drops a user from the current database.
sp_helpgroup Reports information about a particular group, or about all groups in the current database.
sp_helpuser Reports information about a particular user, or about all users in the current database.
sp_locklogin Locks a SQL Server account so that the user cannot log in, or displays a list of all locked accounts.
sp_modifylogin Modifies a SQL Server login's default database, default language, or full name.
sp_password Adds or changes a password for a SQL Server login account.
sp_reportstats Reports statistics on system usage.
sp_role Grants or revokes roles to a SQL Server login account.
sp_who Reports information about a particular user or about all current SQL Server users and processes.


Table 7-17: System procedure for retrieving syntax information
Procedure Description
sp_syntax Displays the syntax of Transact-SQL commands, system procedures, utilities, and language library routines.

ODBC Procedures

 These stored procedures are compatible with the catalog interface for the Open Database Connectivity (ODBC) Applications Programming Interface (API.) These stored procedures return data dictionary information in table form. They provide a uniform catalog interface for accessing database gateways as well as SQL Server.

Table 7-18: ODBC procedures

Procedure Description
sp_column_privileges Returns privilege information for one or more columns in a single table or view.
sp_columns Returns column information for a single table or view in the current database.
sp_databases Lists databases present in the SQL Server installation.
sp_datatype_info Returns information about supported datatypes.
sp_fkeys Returns logical foreign key information for the current database.
sp_pkeys Returns primary key information for a single table in the current database.
sp_server_info Returns a list of attribute names and matching values for SQL Server.
sp_special_columns Returns the optimal set of columns that uniquely identify a row in the specified table, and can also return a list of the columns that are automatically updated when any value in the row is updated.
sp_sproc_columns Returns column information for a single stored procedure in the current database.
sp_statistics Returns a list of all indexes on a single table in the current database.
sp_stored_procedures Returns a list of stored procedures in the current database.
sp_table_privileges Returns table privilege information for a single table in the current database.
sp_tables Returns a list of tables, views, and system tables in the current database.


[Top] [Prev] [Next] [Bottom]