Datasheet

Indexes
Indexes are another way of optimizing performance by letting the DBMS know how you intend to make
use of data. An index is an internally maintained table in the database that enables quick access to a row
(or rows) containing specific data, such as a particular column value, a column value that contains a cer-
tain word, and so on. The exact implementation of an index is specific to the DBMS you are using so you
can’t make any assumptions about exactly how they are stored or how they work. However, you don’t
need to understand how an index is implemented to use it.
Conceptually you can think of an index as a look-up table, where you find rows in the index with a spe-
cific piece of data in one column, and the index then tells you the rows in the indexed table that match
that data. To return to the phone book example, an index could be used to search for records via the
phone number column instead of the name column. You would need to tell the DBMS to create an index
for values in the phone number column because, by default, no indexes are created for a table other than
for primary key values. By building an index based on the phone number column, the DBMS can use a
much faster searching algorithm to locate rows — it no longer has to look at the phone number column
of every row in the address book; instead it looks in the index (which has, effectively, already looked at
every row in the address book) and finds the relevant rows.
The only downside to using indexes is that they need to be stored, so the database size increases.
Indexes also need to be periodically refreshed as data in the table they are indexing changes.
The creation of indexes can be a bit of an art form. In many DBMSes it is possible to tailor indexes to
closely match the queries with which they will be dealing. For example, looking for strings that end with
a certain substring works well with an index built around the last 100 characters of a text column, but
might not even be possible in an index built on the first 100 characters of the same column.
One commonly used type of index is the full-text index. It’s useful when large quantities of text are
stored in columns because the index examines the text in-depth and stores its results. This enables you to
perform searches within text data much faster than would otherwise be possible because you only have
to look at a word in the index rather than looking through all the text in all the columns of the original
data. However, full-text indexes can require large amounts of storage.
Security
Security means a couple of things when talking about databases. For a start, it means not letting other
people get access to your data. For most professional DBMSes, this isn’t something you have to worry
about too much. If your DBMS costs lots of money (and it probably does), you get what you pay for, and
your data is secure.
The other aspect of security in databases is authorizing different users to perform different tasks. In
some cases, such as in SQL Server 2005, you can approach this in a granular way. You can, for example,
assign a user the rights to view data in one table but not to edit that data. You can also restrict access to
individual stored procedures and control access to all manner of more esoteric functionality. Users can
also be authorized to perform tasks at the DBMS level if required — such as being able to create new
databases or manage existing databases.
Most DBMSes also enable you to integrate with existing forms of authentication, such as Windows
account authentication. This allows for single-login applications, where users log on to a network with
11
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 11