The Data Definition Language (DDL) refers to Transact-SQL statements used to define databases and database objects and to manage their properties. In SQL Server 6.0, DDL includes a significant number of statements. The feature areas with significant enhancements can be categorized as shown in the following table. The DDL enhancements are then described in detail.
Feature area |
Related DDL statements and Transact-SQL Reference topics |
|---|---|
| Table definition | ALTER TABLE CREATE TABLE Constraints IDENTITY Property Data Integrity |
| View definition | CREATE VIEW |
| Dropping objects | All drop statements DROP DEFAULT DROP PROCEDURE DROP TABLE |
| Encrypted definition: procedures, triggers, and views | CREATE PROCEDURE CREATE TRIGGER CREATE VIEW syscomments |
| Parameters | CREATE PROCEDURE |
| Local and global temporary objects: procedures and tables | CREATE PROCEDURE CREATE TABLE |
The syntax for the CREATE TABLE and ALTER TABLE statements has been updated to include ANSI-standard constraints and the IDENTITY property. Constraints can enforce primary and foreign key references through declarative referential integrity (DRI) as well as other various aspects of data integrity: entity, domain, and user-defined. The IDENTITY property refers to a column attribute which defines a series of values to be used for inserts.
Earlier releases of SQL Server supported only user-defined datatypes, defaults, rules, triggers, and stored procedures. SQL Server now supports all of these objects, and the table-definition syntax now includes a variety of extensions that achieve similar results without requiring separate object-creation and "binding" steps to enforce them. Constraint information is maintained in new system tables (sysconstraints and sysreferences). All of these options can be used together to provide efficient and effective data integrity.
| Integrity type |
Earlier options |
Comparable, new options |
|---|---|---|
| Entity | Unique indexes | PRIMARY KEY and UNIQUE constraints, IDENTITY Property |
| Domain | Datatypes, defaults, rules | CHECK, DEFAULT and FOREIGN KEY constraints |
| Referential | Triggers | FOREIGN KEY constraints |
| User-defined | Rules, triggers, stored procedures | All column- and table-level constraints in CREATE TABLE |
The syntax for constraints includes support for specifying clustered or nonclustered indexes to support PRIMARY KEY and UNIQUE constraints. For more information about defining and manipulating constraints, and for information about the IDENTITY property, see the ALTER TABLE and CREATE TABLE statements.
Views provide an alternate method for looking at the data within a table or group of related tables. The CREATE VIEW statement in SQL Server 6.0 checks the validity of data updated through a view, and permits the use of the DISTINCT keyword in the definition of the view. For details, see the CREATE VIEW statement.
Procedures, triggers, and views can be created with an optional (one way) encryption parameter that encrypts the definition text stored in syscomments. For details, see the CREATE PROCEDURE, CREATE TRIGGER, and CREATE VIEW statements. In addition, if passwords are stored (that is, not using integrated security), they are now always encrypted.
The DROP object_name statements (DROP DEFAULT, DROP PROCEDURE, DROP TABLE, DROP TRIGGER, etc.) can now be executed by the database owner (DBO) or the system administrator (SA). The DBO and SA no longer need to use the SETUSER statement to drop objects. All other object owner functionality is maintained by the database object owner. The DBO and SA must continue to use SETUSER to perform tasks such as granting permissions.
When executing a stored procedure, the server will reject any parameters that were not included with the parameter list during procedure creation. Any parameter that is passed by reference (explicitly passing the parameter name) will not be accepted if the parameter name does not match. For details on creating procedures and passing parameters, see the CREATE PROCEDURE and EXECUTE statements.
Tables and stored procedures can be created for permanent access by any user who has appropriate permissions, or they can be created as temporary objects. You can create local and global temporary procedures or tables for use only in the current session or for use in all sessions. Local temporary objects are visible in the current session only; global temporary objects are visible to all sessions. Local temporary objects are automatically dropped at the end of the current session; global temporary objects are dropped at the end of the last session using the table (normally, this is when the session that created the object ends). For details, see the sections about the CREATE PROCEDURE and CREATE TABLE statements.