DDL and Stored Procedures

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

Data Definition Language

The SQL language has two main divisions: Data Definition Language (DDL) that is used to define all the objects in an SQL database, and Data Manipulation Language (DML) that 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, for example 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 done using system stored procedures. These are stored procedures whose names start with sp_ or xp_ and are installed when SQL Server is installed. 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 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.