Datasheet

Chapter 1: Being Objective: Re-Examining Objects in SQL Server
❑ 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 generally 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.
A special kind of trigger — called a before trigger — can be associated with a view. We will take a more
in-depth look at these in Chapter 12.
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, compete
with triggers as possible solutions to data integrity issues. They are not, however, the same thing; each
has its own distinct advantages.
Unlike triggers and indexes, constraints can only be associated with tables (no views).
Schemas
Schemas provide an intermediate namespace between your database and the other objects it contains.
The default schema 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, how-
ever, 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 now seems to be featuring their use (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).
Filegroups
By default, all your tables and everything else about your database (except the log) are stored in a single
file. That file is a member of what’s called the primary filegroup. However, you are not stuck with this
arrangement.
7