Table Creation Data Types A Data type is

Table Creation / Data Types

A Data type is. . . Specifies what kind of information a column will hold so that the system will know how the data is to be physically stored and manipulated. Example: an integer data type can only hold whole numbers and can be manipulated arithmetically.

Different Datatypes

Defining Data type Length Defining the length of a data type allocates storage space for that specified data. Using a fixed length data typed allocates the same amount of space regardless of the actual data length. Variable length datatypes can adjust to the length of the data as long as the maximum is not exceeded.

What does this mean? If we define the following fixed datatype: char(10) 10 bytes of storage space is allocated regardless of the actual data length. If we redefine as a variable datatype: varchar(10) the data length can not exceed 10 bytes but the actual storage space will only be as long as the data stored.

Implications? Data type allocation: Data actual char(10) varchar(10) bluhens 7 b 10 b 7 b basketball 10 b americaeast 11 b 10 b champs 6 b 10 b 6 b total 34 b 40 b 33 b

Conclusions • Choose your datatypes and lengths carefully. • Choosing a fixed length can yield higher performance since the system need not maintain allocation. • However, if you know your data will not have a consistent length then a variable length is best as this will conserve space.

Nulls are. . . • Nulls represent data that is absent and/or unavailable. • Nulls are not blank or zero • Assigning null status to a column tells the system to insert a null value if there is no data available • Assigning a ‘not null’ status means that the system will reject any entries that do not provide data for this column. An error is usually generated.

To be null or not to be null? • Is the data essential? • Is the column a primary key? • Would things make sense if the data was not present? • Do other things rely on this data?

4 Steps to Table Creation • Name it • Name the columns it contains • Specify the datatype of each column • Specify the NULL status of each column

Table Creation Syntax SQL statement CREATE TABLE tablename (colname …. …. ) datatype(length) null/not null,

Example create table titles (title_id char(6) title varchar(80) type char(12) not null, null)

Table Creation Process • Decide on datatype (length, precision, etc. ) of each column • Decide which columns should be null • Decide which columns need to be unique • Note foreign/primary key pairings • Make sure permissions are properly assigned

Charting table structure