Tables Fundamentals

Introduction

A computer database is an application that contains one or more lists. A list is a series of values. A simple list can be made of numbers. Another simple list can contain names (of people, or of songs, or of food items). In a formal database, a list is called a table. In some documents, a tale is called an entity. In some other documents, a table is referrred to as a relation.

Microsoft SQL Server provides various means and tools to create tables. First of all, a table must belong to a database. This means that you must first choose the database that will own the table.

We are starting a database for a fictitious university. This is a higher learning institution (college) that provides courses in many areas. It delivers Bachelor’s degrees and Master’s degrees. In this series of lessons, we will limit our school to a 4-year college (Bachelor’s degrees).

Students register to the school to get the desired education. To get admitted to the school, a student must provide information such as the name, the date of birth, the gender, etc. Once admitted, to attend a course, a student must enroll in one or various courses. Each course is taught or supervised by a teacher. When a course ends, the teacher must give (or assign) a grade to each student who attended the course. In most colleges and in ours, a student will receive a letter grade as A or B or C, etc.

There is no standard way a university database is made. Each school does it as it wants. We too will use our inspiration to create and manage our university database.

In this first version of our database, named University1, we will create a table for students’ grades so we can start defining what types of grades the students will get. We will also define what the scale is for each letter grade; that is, what ranges of numbers define a letter grade. The table of grades will be stored in a schema named Academics.

Practical Learning: Introducing Tables

1. Launch Microsoft SQL Server and connect
2. In the Object Explorer, expand the Databases node if necessary.
   In the Object Explorer, right-click Databases and click New Database...
3. In the New Database dialog box, set the Database Name to University1
4. Click OK
5. In the Object Explorer, right-click Databases and click Refresh
6. Click the + button of University1 to expand it
7. Under University1, click the + button of Security to expand it
8. Right-click Schemas and click New Schema...
9. In the Schema Name text box, type Academics
10. Click OK

Visually Creating a Table

One way to create a table is to design it. To do this, in the Object Explorer, expand the Databases node and expand the database to which the table will belong. Right-click the Tables node and click New Table...

This would create a Table window.

In our lessons, if you right-click the Tables node in the Object Explorer and click New Table..., the window that displays will be called the Table window.

Practical Learning: Visually Starting a Table

• In the Object Explorer, under University1, right-click Tables and click New Table...
   
  

Creating a Table With SQL

To assist you with creating a table, the structured query language, SQL, provides an operation, called a command, in its Data Definition Language (DDL). The command is CREATE TABLE and it is used is a formula that starts as follows: CREATE TABLE TableName . . . The CREATE TABLE expression is required. TableName specifies the name of the new table.

Temporary Tables

After creating a table, it becomes part of its database and you can use that table over and over again. In some cases, you may want to create a table to test something and you would not need to use that table the next time you connect to the server. Such a table is referred to as a temporary table.

To create a temporary table, use the poind sign, #, after CREATE TABLE. Once the table has been created, it would be available as long as you are using the same connection to the server. If you close Microsoft SQL Server, the table would be automatically deleted.

Using Sample Code

To assist you with creating a table, Microsoft SQL Server can generate sample code for you. You can then simply modify or customize it. First display or open an empty Query Editor. To display the Templates Explorer, on the main menu, click View -> Templates Explorer. In the Templates Explorer, expand the Table node. Under Table, drag Create Table and drop it in the Query Editor. Sample code would be generated for you.

Primary Characteristics of a Table

 

The Name of a Table

When creating a table, you must give it a name. If you are visually creating a table, if you decide to close it, you would be asked whether you want to save it. If you click Yes, you would be asked to give it a name.

The name of a table:

• Can be made of digits only. For example you can have a table called 148
• Can start with a digit, a letter, or an underscore
• Can be made of letters, digits, and spaces

Besides these rules, you can make up yours. To avoid confusion, here are the rules we will use to name our tables:

• A name will start with a letter. Examples are 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
• Unless stated 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 Staff Members or Video Titles
• Because we consider that a table is a collection of values (a collection of records), the names of our table will be in plural (some people get very upset with it, just as some people make it a big deal that the name of a table be completely in uppercase). Examples are Customers or SeasonalEmployees

The Schema of a Table

As mentioned already, a schema is an object that contains other objects, such as tables. In fact, every table in Microsoft SQL Server must belong to a schema. When you are creating a table, you have the option of specifying or not indicating its schema. If you don't specify a schema, the default dbo schema would own it.

To visually specify the schema that will contain a table, in the Properties window, click Schema, then click the arrow of its combo box and select the desired schema:

To specify the schema of a table using code, precede its name with the name of the schema followed by a period. The formula to use is:

CREATE TABLE SchemaName.TableName....

An example would be:

CREATE SCHEMA Registration;
GO
CREATE TABLE Registration.Students . . .

Remember that if you don't specify a particular schema, the default dbo schema takes ownership of the table.

After creating a table, you can change its schema. To do this visually, open the table in Design view. In the Properties window, click the arrow of the Schema combo box and select the desired schema. You will receive a message box (based on the permissions):

If you still want to change the schema, click Yes.

Practical Learning: Specifying the Schema of a Table

• If the Properties window is not visible, on the main menu, click View -> Properties Window.
  While the table is displaying, in the Properties window, click Schema, then click the arrow of its combo box and select Academics

Tables Maintenance

Introduction

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

Viewing the Properties of a Table

Like every other object of a database or of the computer, a table possesses some characteristics that are proper to it. To view these characteristics, in the Object Explorer, right-click the table and click Properties.

Opening a Table

Most operations require that you open a table before using it. There are various ways a table displays, depending on how you want to examine it:

• To view the structure of a table, perhaps to change its design, in the Object Explorer, expand your database and its Tables node. Right-click the table and click Design. The table would open in design view, the same view you use to visually create a table

In our lessons, if you right-click a table in the Object Explorer and click Design, the window that displays will be referred to as the Design View of a table.

• If you want to view the SQL code of a table, in the Object Explorer, right-click the table, position the mouse on Script Table AS, CREATE To, and click New Query Editor Window

Modifying the Design of a Table

Probably the most routine operation you will perform on a table is to change its design. This is usually easily done after you have opened the table in Design view. You can then change what you want. After making the change(s), you must save the table. Depending on how your database is configured, you may receive an error as "Saving changes is not permitted...":

To avoid this error and make it possible to modify tables, on the main menu of SQL Server Management Studio, click Tools -> Options... In the left tree list of the Options dialog box, click Designers. In the Table Options, clear the Prevent Saving Changes That Require Table Re-Creation check box.

Practical Learning: Allowing Changes on Tables

1. On the main menu of SQL Server Management Studio, click Tools -> Options...
2. In the left tree list of the Options dialog box, click Designers
3. In the Table Options, clear the Prevent Saving Changes That Require Table Re-Creation check box

   

4. Click OK

Tables Review

To see the list of tables of a database in the Object Explorer, you can click the Tables node:

To see the list of tables of a database using SQL, in a Query Editor, specify the database (using a USE statement). On the next line, type sp_help and execute it. Here is an example:

Renaming a Table

If you find out that the name of a table is not appropriate, you can change it: To change the name of a table in the SQL Server Management Studio, in the Object Explorer, right-click the table and click Rename. Type the desired name and press Enter To change the name of a table with code, type sp_rename, followed by the current name of the table, a comma, and the new desired name of the table. Then execute the code. 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 N'StaffMembers', N'Employees'; GO In this case, the database engine 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 doesn't exist, you would receive an error

Deleting a Table

If you have an undesired table in a database, you can remove it:

• To delete a table in the SQL Server Management Studio, in the Object Explorer, right-click the table under its database node and click Delete. You will receive a warning giving you a chance to confirm your intentions. If you still want to remove the table, click OK
• 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

You can also use sample code that Microsoft SQL Server can generate for you. First display an empty Query Editor. Also display the Templates Explorer and expand the Table node. Under Table, drag Drop Table and drop it in the empty Query Editor. Sample code would be generated for you. You can then simply modify it and execute the statement.

Referring to a Table

In your database development assignments, you will write expressions that involve the names of tables. In those expressions, you will need to specify a particular table you want to use. There are three main ways you can do this. To refer to, or to indicate, a table:

• You can simply type its name. An example would be Students
• You can type the schema name, followed by the period operator, followed by the name of the table. An example would be Registration.Students. In reality, if a table belongs to a schema other than dbo, you should always precede its name with its schema
• You can type the name of the database to which the table belongs, followed by the period operator, followed by the schema, followed by the period operator, and followed by the name of the table. An example would be RedOakHighSchool.Registration.Students

Practical Learning: Ending the Lesson

• Close Microsoft SQL Server.
  If asked whether you want to save something, click No

Previous	Copyright © 2000-2022, FunctionX	Next