Part of the
You Can Learn SQL series.
By Ken Brown
Updated: August 31, 2004
A database in Microsoft® SQL Server™ 2000 consists of a collection of tables
with data, and other objects, such as views, indexes, stored procedures, and
triggers, that are defined to support the activities performed with the data.
My definition … a database is a collection of information (data) with software
tools to extract the information in a meaningful way.
It isn’t only the data, it’s the ability to retrieve the data quickly and
manipulate the data to produce useable information.
In the Query Analyzer’s Object Browser, you see the Northwind database and all
of the objects that make up and support the database.
It isn’t just the tables; it is System tables, Views, Stored Procedures,
Functions and User Defined Data Types. All of these objects combine together to
make a database and support a database.
User Tables are the tables created by the user. Table objects contain
columns that define a table, and row data that populate it.
System Tables store information that is used by the database management
system to operate the database. System tables should not be altered directly by
any user. For example, do not attempt to modify system tables with DELETE,
UPDATE, or INSERT statements, or user-defined triggers.
A View is a virtual table whose contents are defined by a query. Like a
real table, a view consists of a set of named columns and rows of data.
However, a view does not exist as a stored set of data values in a database.
The rows and columns of data come from tables referenced in the query defining
the view and are produced dynamically when the view is referenced.
A Stored Procedure is a group of SQL statements compiled into an
operating plan to produce a result set.
There are three types of Functions: Rowset, Aggregate and Scalar. The
function purpose is to return information, perform a calculation, or perform an
operation on a data type. Converting an integer to a string is a function call.
User-defined data types can be used when several tables must store the
same type of data in a column and you must ensure that these columns have
exactly the same data type, length, and null ability.
In review, a database consists of a collection of tables with data, and other
objects, such as views, indexes, stored procedures, and triggers, that are
defined to support the activities performed with the data.