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.

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:

As an alternative to using the Design View of a table, to see the characteristics of a column, in the Object Explorer, expand the database and the Tables node. Expand, the Columns node:

• Right-click the name of the column and click Properties

• Double-click the column

Any of these actions would display the Column Properties dialog box:

Another way to see the properites of a column is bay calling the COLUMNPROPERTY() function. Its syntax is:

COLUMNPROPERTY(id, column, property) RETURNS int/bit;

This function takes three arguments and all are required. All three arguments are passed as strings. The function returns a value that depends on the type of information you want. It can return an integer or a Boolean (bit) value.

The first argument is the integral identifier of the table that holds the column. To get it, you can call the OBJECT_ID() function and pass it the name of the table. The second argument is the name of the column. The third argument specifies the type of information you want. For example, to know the maximum number of characters that a string-based column allows, you can pass the third argument as PRECISION (case-insensitive). Here is an example:

SELECT COLUMNPROPERTY(OBJECT_ID(N'Employees'),
      		      N'LastName',
      		      N'precision')AS [Maximum Number of Characters];
GO

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

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.

To specify the collation of a column when creating in, type COLLATE, followed by the desired collation code. Here is an example:

CREATE TABLE Customers(
    FullName nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
);

When making a change on a column, you are also said to alter the table. To support this operation, SQL starts with the following formula:

ALTER TABLE TableName

When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.

After a table has already been created, you can still add a new column to it.

To visually add a new column in SQL Server Management Studio, in the Object Explorer:

• Expand the database that owns the table that will use the new column. Expand the Tables node. Right-click Columns and click New Column...

• Right-click the table and click Design. Click the first empty field under Column Name

In both cases, in the empty bottom field, enter the necessary information. In SQL, the basic formula to add a new column to a table is:

ALTER TABLE TableName
ADD ColumnName Properties

ColumnName is required. In fact, on the right side of the ADD word, define the column by its name and using all the options we reviewed for columns. Here is an example:

ALTER TABLE StaffMembers
ADD Address nvarchar(100) NULL
GO

When this code executes, a new column name Address, of type nvarchar, with a limit of 100 characters, and that allows empty values, will be added to a table named StaffMembers in the current database.

You can also use sample code to add a new column to a table. First display an empty query window and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. 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

-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	ADD <new_column_name, sysname, column3>
	    <new_column_datatype,, datetime>
	    <new_column_nullability,, NULL>
GO

To visually insert a new column between two existing one, right-click the column that will succeed it and click Insert Column:

This would create a new empty field. Type the desired name and specify the other options.

If you find out that the name of a column is not appropriate, you can change it.

To visually rename a column, in the Object Explorer:

• Expand the database. Expand its Tables node. Expand the Columns node. Right-click the column and click Rename
• Right-click the table that the column belongs to and click Modify. In the design view, highlight the name of the desired column to put it into edit mode and edit it

In SQL, to change the name of a column, first open an empty query window. In a query window, execute sp_rename using the following formula:

sp_rename 'TableName.ColumnName', 'NewColumnName', N'COLUMN'

The sp_rename factor and the 'COLUMN' string are required. The TableName factor is the name of the table that the column belongs to. The ColumnName is the current name of the column. The NewColumnName is the desired name you want to give to the column.

Here is an example:

sp_rename N'StaffMembers.FullName', N'EmployeeName', N'COLUMN';
GO

When this code is executed, the interpreter will look for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, then it renames it EmployeeName.

If you have an undesired column that you don't want anymore in a table, you can remove it. To visually delete a column, in the Object Explorer, expand the database, the Tables, and the Columns nodes. Right-click the undesired column and click Delete. The Delete Object dialog box would display. If you still want to delete the column, click OK. To change your mind, click Cancel.

To delete a column using code, first open or access an empty query window, and use the following formula:

ALTER TABLE TableName
DROP COLUMN ColumnName

On the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example:

ALTER TABLE StaffMembers
DROP COLUMN CurrentResidence;
GO

When this code is executed, the interpreter will look for a column named CurrentResidence in a table StaffMembers of the current or selected database. If it finds that column, it will remove it from the table.

Microsoft SQL Server can also generate sample code you can use to delete a column from a table. Before doing this, first display an empty query window and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--============================================
-- Drop column template
--
-- This template creates a table, then it  
-- drops one of the columns of the table.
--============================================
USE <database, sysname, AdventureWorks>
GO

-- Drop a column from the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	DROP COLUMN <new_column_name, sysname, column3> 
GO

1. Close the query window without saving the file
2. In the Object Explorer, under the Databases node, right-click bcr2 and click Delete
3. In the dialog box, click OK (if you are denied to delete the database, close Microsoft SQL Server, reopen it, and try deleting the database again)

1. What are the main components of a table in a database (Select 2)?
   1. Data Types
   2. Attributes
   3. Columns
   4. Rows
   5. Views
2. Which of the following are keyword in Transact-SQL (Select 2)?
   1. ASM
   2. TYPE
   3. EXPLICIT
   4. DOUBLE
   5. ALTER
3. Which of the following are keyword in Transact-SQL (Select 2)?
   1. COLUMNS
   2. DROP
   3. CONTINUE
   4. REMOVE
   5. TABLE
4. What is the formula to create a table?

   1. CREATE TABLE [SchemaName.]TableName
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options

   2. CREATE OBJECT [SchemaName.]TableName
      (
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      )

   3. CREATE TABLE [SchemaName.]TableName
      (
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      )

   4. EXECUTE TABLE [SchemaName.]TableName
      BEGIN
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      END

   5. CREATE OBJECT:[SchemaName.]TableName
      AS
      BEGIN
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      END

5. What is wrong with the following code?

   CREATE TABLE #Table
   (
   	EmplNbr nchar(6),
   	name nvarchar(60)
   )

   1. Nothing
   2. The name Table cannot be used as the name of a table because Table is a reserved keyword
   3. The code must specify that EmplNbr is unique, which it does not
   4. The name of a column must start in uppercase, as in Name
   5. The creation of a table must end with a semi-colon
6. What is the name of the stored procedure used to change the name of a table?
   1. sp_rename
   2. sp_changename
   3. sp_move
   4. sp_adjust
   5. sp_table
7. What is the expression used to modify the structure of a table?
   1. ADJUST TABLE TableName
   2. MODIFY TABLE TableName
   3. SETUP TABLE TableName
   4. ALTER TABLE TableName
   5. CHANGE TABLE TableName
8. What is wrong with the following code?

   CREATE TABLE Contractors
   (
       ContractorCode nchar(2),
       DateHired date,
       [First Name] nvarchar(20),
       [Last Name] nvarchar(20),
       Hourly Salary money
   );

   1. Nothing
   2. You cannot use a space in the name of a column
   3. When creating a table, you cannot use a mixture of one-word names and names that contain spaces
   4. There is a comma missing after money
   5. The expression Hourly Salary is not well-formed
9. James had already created a table and added a few records to it. Now he wants to add a new column but is concerned after rules of altering a table. What types of columns can be added to a table that has been created already and holds some records?
   1. Any type of column can be added to an existing table
   2. Only columns that are string-based (columns of type char and varchar)
   3. Only columns that don't use unique values
   4. Columns that don't use a CHECK constraint
   5. The name of the new column cannot include a space
10. What's the formula to add a new column to an existing table?

    1. ADD ColumnName Properties
       ALTER TABLE TableName
       

    2. SELECT TABLE TableName
       INSERT ColumnName Properties

    3. ALTER TABLE TableName
       ADD ColumnName Properties

    4. EXECUTE sp_addcolumn ColumnName Properties
       TO TABLE TableName
       

    5. CHANGE TABLE TableName
       INSERT COLUMN ColumnName Properties

1. Answers
   1. Wrong Answer
   2. Wrong Answer
   3. Right Answer
   4. Right Answer
   5. Wrong Answer
2. Answers
   1. Wrong Answer
   2. Right Answer
   3. Wrong Answer
   4. Wrong Answer
   5. Right Answer
3. Answers
   1. Wrong Answer
   2. Right Answer
   3. Wrong Answer
   4. Wrong Answer
   5. Right Answer
4. Answers
   1. Right Answer: There is nothing wrong with that code
   2. Wrong Answer: Yes, Table is a keyword but if you are creating the table as a temporary object, you can precede it with #
   3. Wrong Answer: Although that would be a good idea, it is not a requirement
   4. Wrong Answer: That's not a rule
   5. Wrong Answer: That's a good idea but not a rule
5. Answers
   1. Right Answer: sp_rename is used to rename an object, such as a table
   2. Wrong Answer: There is no such a stored procedure as sp_changename
   3. Wrong Answer:
   4. Wrong Answer:
   5. Wrong Answer:
6. Answers
   1. Wrong Answer: There is no such an expression as AJUST TABLE
   2. Wrong Answer: There is no MODIFY keyword in Transact-SQL
   3. Wrong Answer: There is no SETUP TABLE in Transact-SQL
   4. Right: To modify a table, you start with ALTER TABLE followed by appropriate options
   5. Wrong Answer: There is no CHANGE TABLE expression in Transact-SQL
7. Answers
   1. Wrong Answer: There is something wrong with the code
   2. Wrong Answer: Yes, you can use space in a name, providing you follow some rules
   3. Wrong Answer: Yes, you can use a mixture of one-word names and names that contain spaces
   4. Wrong Answer: There must be no comma after the last column
   5. Right: Either the column's name should be in one word as in HourlySalary or its name must be included in square brackets as in [Hourly Salary]. [HourlySalary] would be valid too
8. Answers
   1. Right: Normally, as long as you follow some rules, any type of column can be added to an existing table
   2. Wrong Answer: Not just string-based columns
   3. Wrong Answer: The rules for adding new columns have nothing to do with unique values
   4. Wrong Answer: Yes, a column that uses a CHECK constraint can be added to an existing table
   5. Wrong Answer: The name of a new column has nothing to do with adding it to an existing table
9. Answers
   1. Wrong Answer: You must start by altering the table
   2. Wrong Answer: There is no need to select anything
   3. Right: Normally, as long as you follow some rules, any type of column can be added to an existing table
   4. Wrong Answer: There is no stored procedure named sp_addcolumn
   5. Wrong Answer: There is no valid CHANGE COLUMN expression in Transact-SQL
10. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer