As you can see from this arrangement, a column is used to particularly classify one type of data. For example, one column can be used to list some names. Another column can be used to list numbers. Yet another column can be used for a select list of items that keep repeating those items. 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.

1. All Computers: Start the computer and log in using your domain account
2. Server:
   1. Launch Microsoft SQL Server. In the Server Name combo box, make sure the name of the computer is selected. In the Authentication combo box, make sure Windows Authentication is selected. Make sure the account you are using is selected in the User Name combo box. Click Connect
   2. In the Object Explorer, expand Databases
   3. To create a new database, right-click Databases and click New Database...
   4. In the Database Name text box, type Exercise3
   5. Click OK
3. Each Student:
   1. Start Microsoft SQL Server
   2. In the Server Name combo box, select the name of the server or type it
   3. In the Authentication combo box, select SQL Server Authentication
   4. In the User Name combo box, type the name of the domain, followed by \, and followed by the login name you were given
   5. In the Password combo box, type your password
   6. Click Connect
4. All Computers: In the Object Explorer, expand the Exercise3
5. All Computers: Under Exercise3, right-click Tables and click New Table...
    
   

To be able to recognize the categories of information that a column holds, the column should have a name. In Microsoft SQL Server, the name of a column displays in the top, the header part, of the column. 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 or col_52_t
• Unless specified otherwise, 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 Date Hired, LastName, Drivers License Number, or EmailAddress

1. Server: As the caret is blinking under the Column Name column, type FirstName
2. pkatts: Type CustomerName
3. gmonay: Type TagNumber
4. rkouma: Type CategoryCode
5. operez: Type OrderDate

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, we saw in Lesson 5 the types of data that Microsoft SQL Server supported. Therefore, you must specify the data type that is necessary for a particular column.

1. All Computers: Click the arrow of the combo box under the Data Type column
2. Server: Scroll down and select nvarchar(50) from the list
    
   
3. pkatts: Scroll down and select nvarchar(50) from the list
4. gmonay: Select nchar(10) from the list
5. rkouma: Scroll down and select nvarchar(50) from the list
6. operez: Scroll down and select date from the list
7. Server: Click the first empty field under FirstName and type MI
8. Server: Press the down arrow key to position the cursor under MI
9. Server: Type LastName and press the down arrow key
10. Server: Type DateHired
11. Server: Press Tab and select date.
12. Server: Press Enter three times to position the mouse cursor under DateHired
13. Server: Type EmployeeNumber and press the down arrow key
14. Server: Complete the table as follows:
     
    

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 stored 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 datetime2 and money data types, you should accept the default length suggested by the database.

There are two ways you can change the length of a string-based column:

• In the top section of the windo, to change the length of the field, in the parentheses of the data type, enter the desired value
• In the top section of the window, click the name of the column. In the bottom section, click the Length field and type the desired value

1. Server: In the top section, click MI to select it
2. Server: In the bottom section, click Length and type 1
3. pkatts: In the bottom section, click Length and type 60
4. gmonay: In the bottom section, click Length and type 12
5. rkouma: In the bottom section, click Length and type 32
6. Server: In the top section of the table, click State and press Tab
7. Server: For the data type, type nchar(2)
8. Server: In the same way, complete the table as follows:
    
   
9. All Computers: To save your table, on the Standard toolbar, click the Save button
10. Server: In the Choose Name dialog box, type Employees
     
    
11. pkatts: Type Customers
12. gmonay: Type Cars
13. rkouma: Type CarsCategories
14. operez: Type OrderProcessing
15. All Computers: Click OK
16. All Computers: Close the table
17. All Computers: On the Standard toolbar, click the New Query button
18. Server: Type the following:

    CREATE DATABASE BethesdaCarRental2;
    GO

19. Server: To create the database, press F5
20. Server: Click the top section of the Query window and press Ctrl + A
21. All computers: Type:

    USE BethesdaCarRental2;
    GO

22. All computers: To execute, press F5
23. All computers: Click inside the top section of the Query window and press Ctrl + A to select everything
24. All Computers: Press Delete

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 [SchemaName.]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 [SchemaName.]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 (appropriate) data types we reviewed.

Remember that some of the data types need to have a length. 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 SQL Server Management Studio, you cannot rely on the default length of strings suggested by SQL. As it happens, the SQL Server Management Studio specifies 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 also saw that you could use sample code to create a table. This allows you to have more control over the various columns you want the table to have. To do this, open an empty query window and display the Templates Explorer. Expand the Table node. Under Table, you can drag Create Table, Add Column, or Drop Column, and drop it in the query window. If you use dropped Add Column or Drop Column, you can delete the undesired sections of the code and isolate only the part that handles table creation. Here is an example:

--==========================================================================
-- Add column template
--
-- This template creates a table, then it adds a new column to the table.
--==========================================================================
USE <database, sysname, AdventureWorks>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
	column1 int, 
	column2 char(10)
)
GO

1. Server: Type the following:
   

   CREATE TABLE Employees (
   	EmplNbr nchar(10), 
   	DateHired DATE,
   	FirstName nvarchar(20),
   	LastName NVARCHAR(20));
   GO

2. pkatts: Type the following:

   CREATE TABLE Customers
   (
   	DrvLicNbr nvarchar(32), 
   	DateIssued DATE,
   	DateExpired date,
   	CustomerName nvarchar(50),
   	CustomerAddress NVARCHAR(120),
   	CustomerCity NvarChar(40),
   	CustomerState NVarChar(50),
   	CustomerPostalCode nvarchar(20),
   	HomePhone nvarchar(20),
   	OrganDonor BIT);
   GO

3. gmonay: Type the following:

   CREATE TABLE Cars
   (
   	TagNumber nchar(12), 
   	CarYear INT,
   	Make nvarchar(40),
   	Model nvarchar(50)
   );
   GO

4. rkouma: Type the following:

   CREATE TABLE CarsCategories(CategoryCode nchar(5), 
   			    Category nvarchar(40);
   GO

5. All Computers: To execute the statement, press F5
6. All Computers: Close the Query window
7. All Computers: When asked whether you want to save the text, click No
8. Server: Close Microsoft SQL Server

In Lesson 5, we saw that, and how, you can create user-defined data types for existing Transact-SQL data types. In Lesson 5, we stored our types in the master database. If you are working on a database, you can create and store your new types in it. As mentioned in Lesson 5, to visually create a UDT, in the Object Explorer, expand your database, expand its Programmability node, and expand its Types node. Under Types, right-click User-Defined Data Types and click New User-Defined Data Type...

In the New User-Defined Data Type dialog box, fill in the necessary information and click OK

Remember that you can also programmatically create the data type(s). Here are examples:

USE Exercise;
GO
CREATE TYPE NaturalNumber FROM int;
GO
CREATE TYPE ShortString FROM nvarchar(20);
GO
CREATE TYPE ItemCode FROM nchar(10);
GO
CREATE TYPE LongString FROM nvarchar(80);
GO
CREATE TYPE Salary FROM decimal(8, 2);
GO
CREATE TYPE Boolean FROM bit;
GO

After creating the UDT(s), you can use it(them) for your column(s). To do this visually, after displaying the table in design view, click the column name, click the arrow of the Data Type combo box to display a mix of Transact-SQL types and your own defined types:

We will write many expressions that include the names of columns. In such expressions, you will need to indicate the particular column you are referring to. There are various ways you can do this. To refer to, or to indicate, a table:

• You must type the name of the table to which the column belongs, followed by the period operator, followed by the name of the column. An example would be Employee.LastName
• You can type dbo, followed by the period operator, followed by the name of the table to which the column belongs, followed by the period operator, followed by the name of the column. An example would be dbo.Employee.LastName
• You can type the name of the database that owns the table's column, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the table to which the column belongs, followed by the period operator, followed by the name of the column. An example would be RedOakHighSchool.dbo.Employee.LastName

You can create an alias name of a table to use in an expression that involves a column.

To visually create an alias for a table, in the Properties window for the table, click Alias and type the desired name:

To create an alias of a table using code, type a letter or a word that will represent the table to which the column belongs. The letter or the word is followed by a period, and followed by the name of the column. An example would be empl.LastName. At the end of the statement, you must type the name of the table, followed by space, and followed by the letter or the word. An example would be Employee empl.

Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc.

To see the structure of a table in the SQL Server Management Studio, in the Object Explorer, you can expand it:

To view the columns of a table using SQL code, in a query window, execute sp_columns followed by the name of the table the columns belong to. Here is an example:

This action displays the list of columns in the COLUMN_NAME column and other characteristics on the right columns.