| Data-definition-language statements (DDL) are a subset of SQL that let you create, modify, and delete database objects. Although most modern databases allow you to do this through a graphical interface, there are times when you might have to do it programmatically, such as within a stored procedure or script. Although a comprehensive discussion of DDL is beyond the scope of this book, we will introduce some of the more-frequently used statements. Note The DDL syntax presented in this section does notwork with Microsoft Access but has been tested successfully on SQL Server. Defining Tables with DDL Statements Three DDL statements are available to define database tables: CREATE TABLE Defines a new table in a database ALTER TABLE Changes a table's structure DROP TABLE Deletes a table from the database
Creating a New Table The CREATE TABLE statement creates a new SQL table in the current database. The following example shows the CREATE TABLE statement for the Person table: CREATE TABLE Person ( SSN char (11) PRIMARY KEY, LastName char (20) NOT NULL , FirstName char (20) NOT NULL , Age int NOT NULL, Address char(30) NULL, City char(30) NULL, State char(2) NULL, Zipcode char (10) NULL ) Notice that each field is listed along with an option to indicate whether Null values are allowed. The entire field list is enclosed in parentheses. The PRIMARY KEY keywords indicate that the SSN field is the primary key for the Person table. Note If you create a table using a SQL command interface, you may need to refresh the table list for it to appear in any already-connected graphical database tools, such as the Server Explorer. Note The SELECT statement can be used to create a new table from records in an existing table using the INTO keyword, as in the following example: SELECT * INTO BackupPerson FROM Person If your database options are set to allow SELECT INTO use, the preceding statement creates a new table called BackupPerson containing the same data and fields as Person. Modifying a Table By using the ALTER TABLE statement, you can add or drop columns from an existing table, change field sizes, and add constraints to your table. Under certain circumstances this may be easier than recreating the entire table and restoring the data. (You can, of course, select the data into a backup table, create the new table, and insert the data back into it.) The following examples show a few typical uses of the ALTER TABLE statement: ALTER TABLE Person ADD Sex Char(1) ALTER TABLE Person ALTER COLUMN LastName char(100) ALTER TABLE Employee ADD CONSTRAINT EmployeePrimaryKey PRIMARY KEY (SSN, Dept) The first example statement adds a new field to the Person table to store a person's sex. The second statement expands the LastName field to 100 characters, leaving the existing data intact. The last line of code creates the primary key for the Employee table. For more uses of the ALTER TABLE statement, see the help file included with your database. Deleting a Table You can delete a table from the database using the DROP TABLE statement. The following SQL statement removes the StateList table from the database: DROP TABLE StateList Note Temporary tables can be created in SQL Server which will be automatically dropped when you disconnect. To create a temporary table, use the pound symbol (#) in front of the table name in a CREATE TABLE or SELECT INTO statement. Note SQL Server 2000 includes the table variable type, which allows you to create variables that act like tables. Memory used by these variables is automatically freed after the execution of your query or stored procedure. |