Datasheet

An index provides us ways of speeding the lookup of our information. Indexes fall into two categories:
❑ Clustered —You can have only one of these per table. If an index is clustered, it means that the
table on which the clustered index is based is physically sorted according to that index. If you
were indexing an encyclopedia, the clustered index would be the page numbers; the informa-
tion in the encyclopedia is stored in the order of the page numbers.
❑ Non-clustered — You can have many of these for every table. This is more along the lines of
what you probably think of when you hear the word
index. This kind of index points to some
other value that will let you find the data. For our encyclopedia, this would be the keyword
index at the back of the book.
Note that views that have indexes —or
indexed views — must have at least one clustered index before
they can have any non-clustered indexes.
Triggers
A trigger is an object that exists only within the framework of a table. Triggers are pieces of logical
code that are automatically executed when certain things, such as inserts, updates, or deletes, happen
to your table.
Triggers can be used for a great variety of things but are mainly used for either copying data as it is
entered or checking the update to make sure that it meets some criteria.
Constraints
A constraint is yet another object that exists only within the confines of a table. Constraints are much like
they sound; they confine the data in your table to meet certain conditions. Constraints, in a way, com-
pete with triggers as possible solutions to data integrity issues. They are not, however, the same thing;
each has its own distinct advantages.
Schemas
Schemas provide an intermediate namespace between your database and the other objects it contains.
The default namespace in any database is “dbo” (which stands for database owner). Every user has a
default schema, and SQL Server will search for objects within that user’s default schema automatically.
If, however, the object is within a namespace that is not the default for that user, then the object must be
referred with two parts in the form of
<schema name>.<object name>.
Schemas replace the concept of “owner” that was used in prior versions of SQL
Server. While Microsoft seems to be featuring their use in this release (the idea is
that you’ll be able to refer to a group of tables by the schema they are in rather than
listing them all), I remain dubious at best. In short, I believe they create far more
problems than they solve, and I generally recommend against their use (I have made
my exceptions, but they are very situational).
6
Chapter 1
04_584340 ch01.qxp 10/18/06 2:11 PM Page 6