Microsoft SQL Server - Lesson 12: Introduction to the Tables of a Database

	Introduction to the Tables of a Database

Tables Fundamentals

Introduction

A table is primarily a list of items or a group of lists. To manage such a list, it should be meticulously organized. To organize this information, it is divided in sections. Here is an example:

Name	Age	Gender	Relationship
Judie	18	Female	Sister
Ernest	24	Male	Cousin
Bill	52	Unknown	Uncle
David	36	Male	Brother
Hermine	12	Unknown	Niece

Based on this, a list is simply an arrangement of information and this information, also called data, is stored in tables.

Visual Creation of a Table

The information of a table is organized in categories called columns and horizontal arrangements called records or rows. A column holds a category of data that is common to all records. A table must have at least one column. This means that you cannot create a table without defining at least one column.

Tables Names

To complete the creation of a table, you must save it. If you are freshly creating a table and decide to save it, you would be prompted to name it. 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

Creating a Table With SQL

Introduction

To assist you with creating a table, you use a Data Definition Language (DDL) command that is CREATE TABLE, followed by a name. Therefore, 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. An example would be:

CREATE TABLE Students . . .

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, start its name with #, followed by the desired name. 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 window. To display the Templates Explorer, on the main menu, you can click View -> Templates Explorer. In the Templates Explorer, expand the Table node. Under table, drag Create Table and drop it in the query window. Sample code would be generated for you.

Tables Maintenance

Introduction

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.

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 it, 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
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
To open a table to view its data, perhaps to perform data entry, in the Object Explorer, right-click the table and click Select Top 1000 Rows

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...":

Save

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:

Options

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 window, specify the database (using a USE statement), and execute sp_help (it is a stored procedure). Here is an example:

Help

Specifying the Schema of a Table

When creating a table, you can specify what schema owns it. Of course, you must have created the schema(s) unless you decide to stick with the existing dbo default schema. To visually assign a schema to a table you are creating, after starting the table, in the Properties window, click Schema, then click the arrow of its combo box and select the desired schema:

Specifying the Alias of a Table

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 . . .

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:

Specifying the Schema of a Table

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

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, 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 N'StaffMembers', N'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 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 window. Also display the Templates Explorer and expand the Table node. Under Table, drag Drop Table and drop it in the empty query window. Sample code would be generated for you. You can then simply modify it and execute the statement.

Referring to a Table

In future lessons, we will write various 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
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

Tables and Permissions

There are many rights you can manage on a table. These include the ability for a developer to create new tables in a database, to modify existing table, to open a table or to delete it.

To visually manage the ability to create tables on a database, access the Database Properties of the database that owns the table. In the Select a Page list, click Permissions. In the Users or Roles list, select the user. In the Permissions list, locater the Create Table row:

Database Properties

After granting or denying access, click OK.

To programmatically allow a user to create tables on a database, type GRANT CREATE TABLE TO followed by the name of the user. Here is an example:

USE Exercise1;
GO
GRANT CREATE TABLE
TO [Raymond Kouma];
GO

To visually manage the permissions on an existing table, in the Object Explorer, right-click the table and click Properties. In the Users or Roles list, select a user. Then view the permissions in the lower list view:

Table Properties

We will come back to the permissions of a table.

Exercises

Lesson Summary Questions

What symbol is used to create a temporary table?
1. @
2. #
3. %
4. &
5. !
What object contains all the data of a database?
1. database
2. table
3. view
4. trigger
5. index
What permissions can be used on a table (Select 3)?
1. CONNECT
2. SELECT
3. DELETE
4. DESIGN
5. UPDATE
Which ones of the following are database permissions (SELECT 3)?
1. ALTER ANY TABLE
2. ALTER ANY USER
3. JUSTIFY
4. AUTHENTICATE
5. TAKE OWNERSHIP
What permission of a database is related to a table?
1. CREATE TABLE
2. ALTER ANY TABLE
3. REFERENCE TABLE
4. SHOW TABLES
5. UPDATE RECORDS
What is the expression used to start creating a table?
1. ADD TABLE TableName
2. CREATE TABLE TableName
3. SETUP TABLE TableName
4. SELECT TABLE TableName
5. TRIGGER TABLE TableName

What is the formula used to rename a table?

RENAME ExistingTableName TO TableNewName;

EXECUTE sp_change ExistingTableName, TableNewName;

sp_changename ExistingTableName, TableNewName;

sp_rename ExistingTableName, TableNewName;

CHANGE FROM ExistingTableName TO TableNewName;

What's the basic formula to delete a table?

```
DELETE OBJECT:TableName
```
```
REMOVE TABLE TableName
```
```
DROP TABLE TableName
```
```
DROP TABLE SET TableName = NULL
```
```
DELETE TABLE = TableName
```

Answers

Answers
1. Wrong Answer: @ is used with variables
2. Right Answer: To create a temporary table, start its name with #
3. Wrong Answer: % has nothing to do with temporary tables
4. Wrong Answer: & has nothing to do with creating a temporary table
5. Wrong Answer: ! has nothing to do with temporary tables
Answers
1. Wrong Answer:
2. Right Answer: That's the right formula
3. Wrong Answer:
4. Wrong Answer:
5. Wrong Answer:
Answers
1. Wrong Answer: The CONNECT permission is not part of a table
2. Right Answer: The SELECT permission can be used on a table
3. Right Answer: DELETE is valid table permission
4. Wrong Answer: There is no so a permission as DESIGN
5. Right Answer: The UPDATE permission exists for tables
Answers
1. Wrong Answer: There is not ALTER ANY TABLE permission in Transact-SQL
2. Right Answer: The ALTER ANY USER permission allows a person to permissions on a user account
3. Wrong Answer: There is no permission named JUSTIFY in Transact-SQL
4. Right Answer: AUTHENTICATE is a valid permission
5. RRight Answer: There is a permission named TAKE OWNERSHIP
Answers
1. Right Answer: The CREATE TABLE permission allows to left a user create tables on a database or it can be used to prevent a user from creating tables
2. Wrong Answer: There is no ALTER ANY TABLE permission in Transact-SQL
3. Wrong Answer: There is no permission named REFERENCE TABLE
4. Wrong Answer: There is no SHOW TABLES permission
5. Right Answer: There is no UPDATE RECORDS permission
Answers
1. Wrong Answer:
2. Wrong Answer:
3. Wrong Answer:
4. Right Answer: That's the right formula
5. Wrong Answer:
Answers
1. Wrong Answer: There is no such expression as ADD TABLE
2. Right Answer: To create a table, you start with CREATE TABLE followed by the appropriate options
3. Wrong Answer: There is no SETUP TABLE expression to create a table
4. Wrong Answer: It is wrong to use SELECT in such an expression
5. Wrong Answer: The TRIGGER and the TABLE keywords are not combined like that
Answers
1. Wrong Answer:
2. Wrong Answer:
3. Right Answer: That,s the right formula
4. Wrong Answer:
5. Wrong Answer: