Creating Tables

Although you will completely cover how to create a table in tomorrow's lesson, you need to know a bit about it in order to work with the remaining topics in today's lesson. When creating a table, you must define several attributes for each column in the table. The first thing you need to decide and define is the data type for the column. The title table has a title column that is defined as varchar and a price column that is defined as money. If you execute an sp_help on the table, you will see the data types for each column.

The next attribute would be the length for the data types that require it. Data types such as datetime, integer, smallinteger, and money have fixed lengths, so you do not need to set a length. Character fields and variable length fields require you to set a maximum length.

The final attribute you need to specify is whether you want the column to be able to accept NULLs. If you want to allow a row of data to be inserted without specifying val ues for some of the columns, NULLs can be allowed in these nonessential columns.

The following is the code we will use to create the customer_demo table:

Use northwind
create table customer_demo(
    cust_id int not null,
    fname varchar(30) not null,
    lname varchar(30) not null,
    addr1 varchar(40) null,
    addr2 varchar(40) null,
    city varchar(40) null,
    zip char(9) null,
    )

As you can see in the preceding code, seven columns are being defined in the new customer_demo table. After the table is created, we can add new rows to the table by providing the cust_id, fname, and lname. The other columns are defined as NULL, so we do not have to specify them if we don't have that information. I will not go into any more detail today; you will cover the CREATE TABLE statement on Day 8, "Defining Data."