DDL and Stored Procedures

Transact-SQL is the language used for all commands sent to Microsoft® SQL Server™ 2000, from all applications. Transact-SQL contains statements that support all administrative work done in SQL Server. These statements fall into two main categories:

Data Definition Language (DDL)

The SQL language has two main divisions: Data Definition Language (DDL), which is used to define and manage all the objects in an SQL database, and Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL. The Transact-SQL DDL used to manage objects such as databases, tables, and views is based on SQL-92 DDL statements, with extensions. For each object class, there are usually CREATE, ALTER, and DROP statements, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. Permissions are controlled using the SQL-92 GRANT and REVOKE statements, and the Transact-SQL DENY statement.

System stored procedures

Administrative tasks not covered by the SQL-92 DDL are typically performed using system stored procedures. These stored procedures have names that start with sp_ or xp_, and they are installed when SQL Server is installed. Some examples of system stored procedures are:

• sp_addtype (Defines a user-defined data type.)
  
  
• sp_configure (Manages the server configuration option settings.)
  
  
• xp_sendmail (Sends an e-mail or page.)

SQL Server 2000 also exposes the SQL-DMO, SQL-NS, DTS, and Replication Component APIs. These are all comprised of OLE Automation objects that encapsulate either DDL or system stored procedures. When an application calls one of the objects, the object actually translates the request to one or more Transact-SQL DDL or system stored procedure statements that are then sent to the server.