The information of a table is organized in categories called columns. To visually create a table:

• In the Object Explorer of the SQL Server Management Studio in Microsoft SQL Server, expand the database for which you are creating the table. Then right-click the Tables node and click New Table. 
• In Microsoft Visual Studio, in the Server Explorer, expand the connection to the database that will own the table. Right-click the Tables node and click Add New Table:

After clicking Add New Table, a new empty table would come up and expect you to take charge.

In SQL, to create a table, you start with the following statement:

CREATE TABLE TableName;

The CREATE TABLE expression is required. The TableName factor specifies the name of the new table. The TableName can use the rules and suggestions we reviewed for the tables.

We saw that a list could be organized in categories called columns. Here is an example:

As you can see from this arrangement, a column is used to particularly classify one type of data. To organize the information that a column holds, a table needs a series of details about each column.

To be able to recognize the categories of information that a column holds, the column must have a name. 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

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, you must specify the data type that is necessary for a particular column. Here is an example:

A database deals with various types of data. Columns that carry the same data type can have different lengths.

Bit Fields: 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.

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

• In the top section of the window, 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

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)

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 the column follows the rules and suggestions we listed. 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.

Here is an example:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
      Using Connect As SqlConnection = _
	      New SqlConnection("Data Source=(local);" & _
     				"Database='Exercise';" & _
     				"Integrated Security=yes;")
        Dim Cmd As SqlCommand = _
	    New SqlCommand("CREATE TABLE Customers (" & _
            		   "DrvLicNbr VarChar(50), " & _
           		   "DateIssued DateTime," & _
           		   "DateExpired DateTime," & _
         		   "FullName varchar(120)," & _
             		   "Address VARCHAR(120)," & _
             		   "City varchar(50)," & _
           		   "State varchar(100)," & _
           		   "PostalCode varchar(20)," & _
             		   "HomePhone varchar(20)," & _
            		   "OrganDonor bit);", _
             		   Connect)
        Connect.Open()
        Cmd.ExecuteNonQuery()

        MsgBox("A new table named ""Customers"" has been created.")
      End Using
    End Sub
End Class

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 Employees.LastName
• You can type the name of the schema, such as 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.Employees.LastName
• You can type the name of the database that owns the table's column, followed by the period operator, followed by the schemaa 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.Employees.LastName

You can create an alias name of a table to use in an expression that involves a column. To do this, 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 operator, 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 Microsoft Visual Studio, in the Server Explorer, expand the connection to the desired database, expand the Tables node, and expand the table whose columns you want to see. Here is an example:

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.

Because different languages use different mechanisms in their alphabetical 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.

To visually specify the language collation of a column, after selecting the column in the top section, in the lower section, click Collation and click the ellipsis button of its right field:

After making your selections, you can click OK.

To specify these options using SQL, when programmatically creating the table, type COLLATE, followed by the desired collation code. Here is an example:

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

Table maintenance consists of reviewing or changing its aspects. This includes reviewing the list of tables of a database, renaming a table, or deleting it.

In Microsoft Visual Studio, to see the list of tables of a database, in the Server Explorer, expand the connection to the desired database and expand the Tables node. Here is an example:

Using Microsoft SQL Server Management Studio, to see the list of tables of a database using SQL, in a Query window, specify the database (using a USE statement), and execute sp_help (it is a stored procedure).

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using connection As SqlConnection = _
  New SqlConnection("Data Source=(local);" & _
   "Database='Exercise';" & _
   "Integrated Security=yes;")
            Dim command As SqlCommand = _
         New SqlCommand("sp_help", connection)
            connection.Open()
            Dim rdr As SqlDataReader = command.ExecuteReader()

            While rdr.Read()
                lbxTables.Items.Add(rdr(0))
            End While

            rdr.Close()
        End Using
    End Sub
End Class

If you find out that the name of a table is not appropriate, you can change it. To change the name of a table with code, execute sp_rename, followed by the current name of the table, a comma, and the new desired name of the table. The formula to use is:

sp_rename ExistingTableName, TableNewName;

The names of tables should be included in single-quotes. Here is an example:

sp_rename 'StaffMembers', 'Employees';
GO

In this case, the interpreter would look for a table named StaffMembers in the current or selected database. If it finds it, it would rename it Employees. If the table does not exist, you would receive an error.

If you have an undesired table in a database, you can remove it. To delete a table using SQL, use the following formula:

DROP TABLE TableName

The DROP TABLE expression is required and it is followed by the name of the undesired table. When you execute the statement, you will not receive a warning before the table is deleted.

There are three main ways you can refer to a table. To refer to, or to indicate, a table:

• You can simply type its name. An example would be Students
• You can type its schema, such as dbo, followed by the period operator, followed by the name of the table. An example would be dbo.Students
• You can type the name of the database to which the table belongs, followed by the period operator, followed by its schema, followed by the period operator, and followed by the name of the table. An example would be RedOakHighSchool.dbo.Students

When making a change on a column, you are also said to alter the table. To programmatically change a column, the 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. In SQL, the basic formula to add a new column to an existing table is:

ALTER TABLE TableName
ADD ColumnName Properties

The ColumnName factor is required. In fact, on the right side of the ADD operator, define the column by its name and use all the options we reviewed for columns.

Here is an example:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Using Connect As SqlConnection = _
	      New SqlConnection("Data Source=(local);" & _
     			 	"Database='Exercise';" & _
     				"Integrated Security=yes;")
        Dim Cmd As SqlCommand = _
              New SqlCommand("ALTER TABLE Customers " & _
        		     "ADD EmaillAddress nvarchar(50);", _
               Connect)
        Connect.Open()
        Cmd.ExecuteNonQuery()

        MsgBox("A new column named ""EmailAddress"" has been added.")
    End Using
    End Sub
End Class

When this code is executed, a new column named Address, of type nvarchar, with a limit of 50 characters, that allows empty entries, will be added to a table named StaffMembers in the current database.

If you find out that the name of a column is not appropriate, you can change it. In Transact-SQL, in a query window, execute sp_rename using the following formula:

sp_rename 'TableName.ColumnName', 'NewColumnName', '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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim strConnection As String = _
	"sp_rename 'Customers.DateExpired', 'ExpirationDate', 'COLUMN';"
        Dim Connect As SqlConnection = _
            New SqlConnection("Data Source=(local);" & _
        	              "Database='Exercise';" & _
		              "Integrated Security=yes")
        Dim cmdDatabase As SqlCommand = _
		New SqlCommand(strConnection, Connect)
        Connect.Open()

        cmdDatabase.ExecuteNonQuery()

        MsgBox("The DateExpired column has been renamed to ExpirationDate")
        Connect.Close()
    End Sub
End Class

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 programmatically delete a column, 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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Using Connect As SqlConnection = _
	    New SqlConnection("Data Source=(local);" & _
     			      "Database='Exercise';" & _
     			      "Integrated Security=yes;")
        Dim Cmd As SqlCommand = _
             New SqlCommand("ALTER TABLE Customers " & _
        	            "DROP Column DateIssued;", _
               		    Connect)
        Connect.Open()
        Cmd.ExecuteNonQuery()

        MsgBox("The column named ""DateIssued"" has been deleted.")
    End Using
    End Sub
End Class

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