Page 68 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Temporary Tables

You can also create temporary tables. Temporary tables are similar to permanent tables, except
temporary tables are stored in tempdb and are deleted automatically when no longer in use.
The two types of temporary tables, local and global, differ from each other in their names, their
visibility, and their availability. Local temporary tables have a single number sign (#) as the
first character of their names; they are visible only to the current connection for the user; and
they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000.
Global temporary tables have two number signs (##) as the first characters of their names; they
are visible to any user after they are created; and they are deleted when all users referencing the
table disconnect from SQL Server.

For example, if you create a table named employees, the table can be used by any person who
has the security permissions in the database to use it, until the table is deleted. If you create a
local temporary table named #employees, you are the only person who can work with the table,
and it is deleted when you disconnect. If you create a global temporary table named ##employees,
any user in the database can work with this table. If no other user works with this table after you
create it, the table is deleted when you disconnect. If another user works with the table after you
create it, SQL Server deletes it when both of you disconnect.

Table Properties

You can define up to 1,024 columns per table. Table and column names must follow the rules for
identifiers; they must be unique within a given table, but you can use the same column name in
different tables in the same database. You must also define a data type for each column.
Although table names must be unique for each owner within a database, you can create multiple
tables with the same name if you specify different owners for each. You can create two tables
named employees and designate Jonah as the owner of one and Sally as the owner of the other.
When you need to work with one of the employees tables, you can distinguish between the two
tables by specifying the owner with the name of the table.

Creating a Table

To create a table use can use any of the three tools in SQL server:
1. Transact-SQL
2. Enterprise Manager

3. SQL-DMO

Modifying Tables

After a table is created, you can change many of the options that were defined for the table when
it was originally created, including:

Columns can be added, modified, or deleted. For example, the column name, length, data
type, precision, scale, and nullability can all be changed, although some restrictions exist.
PRIMARY KEY and FOREIGN KEY constraints can be added or deleted.

UNIQUE and CHECK constraints and DEFAULT definitions (and objects) can be added or
deleted.

62 LOVELY PROFESSIONAL UNIVERSITY