The Columns of a Table

To organize the information that a column holds, a table needs a series of details about each column. Two aspects are particularly important: a name and the type of data that a column should/must/can hold.

The name of a column allows the database as a file to identify the column. The name of a column also will help you, the database developer, to identify that column. There are rules and suggestions you must or should follow when naming the columns of a table.

The name of a column:

• can start with a letter, a digit, or an underscore
• Can include letters, digits, and spaces in any combination

After respecting these rules, you can add your own rules. In our lessons, here are the rules we will use to name our columns:

• A name will start with a letter. Examples are n, act, or Second
• After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are n24, act_52_t
• A name will not include special characters such as !, @, #, $, %, ^, &, or *
• If the name is a combination of words, each word will start in uppercase. Examples are DateHired, RealSport, or DriversLicenseNumber

1. Under the Column Name column, double-click name to highlight it
2. Type FirstName to replace it and press Enter

After deciding on the name of a column, the database needs to know what kind of information the column would hold. Since there are various kinds of information a database can deal with, SQL Server provides a set of categories called data types. Therefore, you must specify the data type that is necessary for a particular column.

1. Click the arrow of the combo box under the Data Type column
2. Scroll down and select varchar from the list
    
   
3. Click the first empty field under FirstName and type MI
4. Press the down arrow key to position the cursor under MI
5. Type LastName and press the down arrow key
6. Type DateHired
7. Press Tab and type d
8. Notice that the datetime data type is selected.
9. Press Enter three times to position the mouse cursor under DateHired
10. Type EmployeeNumber and press the down arrow key
11. Complete the table as follows:
     
    
12. Save the table

The Length of Data

A database deals with various types of data, appropriate or not for certain fields. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be entered in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths.

Bit Fields: We saw already that a bit column type is meant for one of two answers. The user is supposed to simply let the database know that the answer is yes or no, true or false, on or off, 1 or 0. Therefore, the only length of this field is 1.

Integers: The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Decimal and Floating-Point Numbers: The Length specifies how many bytes the field can store.

Strings: The Length of a character or string column specifies the maximum number of characters that the field can hold.

In some circumstances, you will need to change or specify the length as it applies to a particular field. For example, since you should use the varchar data type for a string field whose content will change from one record to another, not all varchar columns need to have the same length. Although a First Name and a Book Title columns should use the varchar type, both columns would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both fields would use the same data type but different lengths. On the other hand, for columns of datetime and money data types, you should accept the default length suggested by the database. 

Practical Learning: Setting Data Types

1. Double-click the Length field for the EmployeeNumber to highlight it and type 6 to replace it
2. Press the down arrow key and type 100 for the Length of the Address field.
3. In the same way, complete the table as follows:
    
   
4. Save the table

The Nullity of a Field

During data entry, users of your database will face fields that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not provide it to the user. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it.

A field is referred to as null when no data entry has been made to it:

• Saying that a field is null doesn't mean that it contains 0 because 0 is a value.
• Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value.

A field is referred to as null if there is no way of determining the value of its content (in reality, the computer, that is, the operating system, has its own internal mechanism of verifying the value of a field) or its value is simply unknown. As you can see, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table. Remember that even if a field is empty, you should know what value it is holding because being empty could certainly mean that the field has a value.

To solve the problem of null values, SQL Server proposes one of two options: allow or not allow null values on a field. For a typical table, there are pieces of information that the user should make sure to enter; otherwise, her data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, you must make sure the field doesn't allow null values; this will ensure that you know that the field is holding a value and you can find out what that value is. This is enforced by clearing the Allow Nulls check box for a field. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, you can allow the user to leave it null. This is done by checking the Allow Nulls check box for the field.

Practical Learning: Applying Fields Nullity

1. To apply the nullity of fields, change the table as follows:
    
   
2. Save the table
3. Close the table

Programmatic Creation of Columns

In the previous lesson, we saw that the primary formula to create a table was:

CREATE TABLE TableName

After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(". The list ends with a closing parenthesis ")". Each column must be separated from the next with a comma, except for the last column. You can include all columns on the same line if possible as follows:

CREATE TABLE Country(Column1, Column2, Column3)

Alternatively, to make your statement easier to read, you should create each column on its own line as follows:

CREATE TABLE Country(
Column1,
Column2,
Column3);

There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:

ColumnName DataType Options

The name of a column should follow the same rules and suggestions we reviewed for the columns.

After typing the name of the column, type the desired or appropriate data type for the column. For this example, use one of the (that is the appropriate) data types we reviewed.

Remember that some of the data types need to have a length. This is certainly true for all string or text-based columns (char, text, varchar, etc). In the case of text-based columns, when using SQL to create your columns, because it is less visual than the table design of the Enterprise Manager, you cannot rely on the default length of strings suggested by SQL (in fact, in MySQL, you must specify a length for varchar). As it happens, SQL Query Analyzer and the New Table window specify different default values for text-based columns. Therefore, when using SQL to create your columns, you should (strongly) specify your own default length for text-based columns.

We saw that some fields would allow blank values, called nulls. If you don't want a column to have empty fields, specify its option as NOT NULL remember that SQL is not a case-sensitive language, but we type SQL own words in uppercase to make them distinct from our own words). If a field can afford null values, you do not have to specify that as an option.

Practical Learning: Creating a Table Using the SQL Query Analyzer

1. In the left frame of SQL Server Enterprise Manager, click BCR to make sure it is selected. On the toolbar, click Tools -> SQL Query Analyzer
2. In the combo box of the toolbar, make sure that BCR is selected
   To create a new table, on the toolbar, click the arrow of the New Query button -> Create Table -> Create Table Basic Template 
3. Change the template code as follows:
    

   -- ============================================= -- Table: Customers -- ============================================= IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Customers' AND type = 'U') DROP TABLE Customers GO CREATE TABLE Customers ( DrvLicNbr VarChar(50) NOT NULL, DateIssued DateTime NOT NULL, DateExpired DateTime NULL, FullName varchar(120) NOT NULL, Address VARCHAR(120) NOT NULL, City varchar(50), State varchar(100), PostalCode varchar(20), HomePhone varchar(20), OrganDonor bit) GO

4. To execute the statement, press F5
5. Close the SQL Query Analyzer window
6. When asked whether you want to save the text, click Yes
7. Type Customers as the name of the file and press Enter

Properties of Columns

Introduction

A column on a table controls what kind of data is appropriate for that particular column. The characteristics that identify or describe such a table are defined as its properties. As we have seen previously, three primary properties are particularly important and required for each column: the name, the data type, and the length. Besides these, some other properties can be used to further control the behavior of a particular field.

Besides the name, data type and length of a column, you can control the columns of a table using the Columns property sheet in the lower section of the table in Design View. These properties sometimes depend on the data type of the column. Therefore, to specify the properties of a column, you must first select it in the upper section of the table. This selection can be done by just clicking either the name, the data type, or the length of the column. Then you can either press F6 or click the first field in the lower section, select the desired property and type the necessary value:

Description

Description: Common and enabled for all fields, the description is used for a sentence that describes the column. You can type anything on that field.

Precision

A precision is the number of digits used to display a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. The Precision field is used for columns that hold numeric values (integers and real numbers). If the data type is specified as an integer (the int and its variants) or a floating-point number (float and real), the precision is fixed by the database and you can just accept the value set by SQL Server. For a decimal number (decimal or numeric data types), SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38 (28 if you are using SQL Server 7).

Scale

A real number is a number that has a fractional section. Examples are 12.05 or 1450.4227. The scale of a number if the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The Scale property is used only for numbers that have a decimal part, which includes currency (money and smallmoney) and decimals (numeric and decimal). If the data type is set as money or smallmoney, the scale is fixed to 4. If the column is of a decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18.

Other Properties

Is RowGuid: This property allows you to specify that a column with the Identity property set to Yes is used as a ROWGUID column.

Collation: Because different languages use different mechanisms in their alphabetic characters, this can affect the way some sort algorithms or queries are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table.