In this demonstration we will be using Data Definition Language (DDL) statements to Create, Alter, and Drop a Table. The first step in creating a table is to give it a name. This is done by using the CREATE TABLE statement and then specifying the name. Next we add in the columns that will store data for the table. In the example above five columns are being created.
The first column will be named OrderID that will have an integer data type. The first column will also be an IDENTITY field. Which means that the values for that column will be automatically inserted as records are added to the table. The OrderID column will also be a Primary Key, which means that this will be the field that will uniquely identify each record. Finally since the Order ID is a Primary Key it will not allow NULL or blank values.
The next four columns, CustID, ProductID, Price, and Qty will then be created with different data types. You may notice that the Qty field will allow for NULL values. Finally, whenever you are using a DDL statement (CREATE, ALTER, DROP) those statements must be separated into their own batches. We accomplish this in T-SQL by using the GO statement.
After you have created the table, it may need to be modified. As you see below, the first step for any modification is to use the ALTER TABLE statement and then specify which table you want to modify. The next line we use the ADD keyword to add the EmpID field as a tiny integer data type that does not allow null values. Observe we do not use the COLUMN statement to ADD a column.To modify an existing column we would use the ALTER COLUMN statement. In the example below, we modify the existing column named CustID that was originally an integer data type to a column that will now be a tiny integer data type.
To remove an existing column, for example the PRICE column, we would use the DROP COLUMN statement.
Finally, if we no longer need to use the table we would use the DROP TABLE statement to remove the entire table from the database.