Databases Reference
In-Depth Information
FOR EXAMPLE
Choosing the Correct Index Keys
You have a table with a two-column identifier, PartNumber and Salesper-
sonNumber. You want to create a clustered index to optimize data retrieval,
but you need to know which column you should list first when you create
the index. What should you do?
The first column listed has the most effect on how the data is physically
sorted. Before you can make your decision, you need to know one thing:
When data is retrieved from the table, in what order is the data usually
needed? If you usually need the data in PartNumber order, that's your first
column. If you need it in SalespersonNumber order, then that's your choice.
If the data retrieval requirements are too evenly split to tell for sure, then
choose based on selectivity. If it's still too close to call, the choice comes
down to your personal preference. If the order doesn't matter, then you
should probably create a nonclustered index instead of a clustered index.
The SQL command to create a view is the
CREATE VIEW command.
In
its simplest form you specify the view name, base object or objects, and columns
to include in the view. The view content is defined through a query based on a
SELECT statement. For example, to create the view we just described, you could
use the following statement:
CREATE VIEW v_LIMIT_SALESPERSON AS
SELECT [Salesperson Number],
[Salesperson Name], Office
FROM SALESPERSON
As you can see in the SELECT statement column list, the column names
immediately following the SELECT statement, the query returns the three
columns specified as making up the view. Salesperson Number and Salesperson
Name are enclosed in square brackets so that the command will recognize and
process the names correctly. This is needed because the names include embed-
ded spaces. Whenever a user accesses data through this view, the SELECT state-
ment executes and the specified columns are returned.
To improve performance when accessing data through a view, SQL Server
supports a type of view called an indexed view that
persists
the view data. In
other words, it stores the result of the SELECT query on disk for faster retrieval.
There is no reason to run the query unless the underlying table data changes.
SQL Server places several restrictions on the creation and use of indexed views,
so they cannot be used in all situations. Also, as we have already seen, there are
usually trade-offs when addressing a design issue like access performance. In this
