The first step in creating tables in SQL is to give the table a name. This is done by using the CREATE TABLE statement and then specifying the name. Next, we add columns that will store data for the table. In the example above five columns are being created.

CREATE TABLE dbo.Orders
(OrderID int IDENTITY PRIMARY KEY,
 CustID int NOT NULL,
 ProductID int NOT NULL,
 Price smallmoney NOT NULL,
 Qty tinyint NULL)
GO

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.

ALTER TABLE dbo.Orders
ADD EmpID tinyint NOT NULL

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.

ALTER TABLE dbo.Orders
ALTER COLUMN CustID tinyint NULL

To remove an existing column, for example the PRICE column, we would use the DROP COLUMN statement.

ALTER TABLE dbo.Orders
DROP COLUMN Price

Finally, if we no longer need to use the table, we will use the DROP TABLE statement to remove the entire table from the database.

DROP TABLE dbo.Orders