Datasheet

Object Description
User-Defined Functions A group of SQL statements that can be encapsulated into a
subroutine that can be called by views and stored procedures.
User-Defined Data Types User-defined data types are based on system data types and
enable you to create a data type with attributes that can be
applied to all your tables.
User Identifies a user with a database.
Roles A group containing certain permissions in the database to
which you add users, effectively assigning the same permissions
to each user.
Tables
Tables are core objects that exist in the data file and contain information about your business. For example,
you could create an employee table like the one shown in Figure 1-1 that contains information about each
employee in your organization.
Each table that you define is made up of columns and rows. Each column represents an attribute about
the information stored in your table, such as an employee’s first or last name. Collectively, the columns
form a row in your table that represents a single occurrence of the information that the table represents.
(Refer to the Employees table in Figure 1-1.)
Keys
Each table in your database usually, but not necessarily, has a column that uniquely identifies each row
of data with a primary key. No two rows in a table can contain the same primary key, and SQL Server
enforces this rule. Primary key columns are usually defined using a globally unique identifier (Guid),
which is a unique value generated based on internal values in your computer. No two computers will
ever generate the same unique identifier.
Primary keys may also contain other values such as an employee’s employee number, which could consist
of alpha and numeric characters. Also, primary key columns cannot contain
NULL values. A NULL value is
one missing: it does not exist.
When a primary key is created on a table, SQL Server automatically creates a unique index for the primary
key on the table. Creating a unique index ensures that no two primary keys can contain the same value.
Indexes are covered in detail in the next section. Using the index on the primary key column provides fast,
efficient access to the data when using the primary key to access data in a table.
Foreign keys point to the primary key in another table. A foreign key in one row of a table points to an
exact row of data in another table. A foreign key value cannot be inserted into a table if the row of data
that it is pointing to in another table does not exist. This is just one of the constraints placed on foreign
keys that help ensure referential integrity.
Referential integrity enforces the defined relationships between tables when records are inserted or
deleted. You cannot insert a foreign key value for a row of data that does not exist in another table.
Referential integrity also prevents you from deleting a row of data that is referenced by a foreign key. To
5
Databases
04_58894x ch01.qxd 10/13/05 5:54 PM Page 5