Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 205

example, using a uniqueidentifier as the primary key on the Order table means that a new order
can be created (including primary key) entirely on the client. Thus, there is no need for a round
trip to the server to get the ID of the newly inserted Order row. This is a huge advantage.

Here's some sample .NET code to generate a GUID value.

❑

C#
System.Guid g;
g = System.Guid.NewGuid();
txtGUID.Text = g. ToString();

VB.NET
Dim g As System.Guid
g = System.Guid.NewGuid()

ComputedColumns
Computed columns are not physically stored in a table (unless it's marked PERSISTED). They are expres-
sions that usually use other columns in the same table. They can also utilize functions such as GETDATE()
or user defined functions. For example, a table may contain columns for length, width, and depth. A com-
puted column called volume could be defined as length

× width × depth. The volume column wouldn't

take up any storage space in the table.

Every time a query retrieves a computed column, SQL Server must calculate the result first. In some
instances, however, this may be desirable. The computation takes place within the SQL Server, not the
query processor. Also, the computation is defined in one place, so it's easier to maintain and provides
greater consistency.

Computed columns cannot be used as foreign keys. They can be used in an index or as part of a primary
key if the value is deterministic and precise.

In SQL Server 2005 a computed column can be defined with the PERSISTED option. This means that SQL
Server will actually store the value. This does take up storage space. This might be desirable if the server
is overworked by continually calculating computed column results. Also, persisting a computed column
allows it to be indexed if it's not precise (it still has to be deterministic). Finally, this would be a good way
to define a calculated field once, instead of possibly multiple times in an application tier.

I/O is usually the most common cause of poor query performance. Computed columns can help alleviate
that because the value of the column isn't actually stored (except for PERSISTED computed columns),
which means that less I/O is needed to retrieve data. However, SQL Server will have to evaluate the
computed column when it's retrieved. The reduction in I/O will usually more than offset the cost of this
evaluation.

Also, computed columns aren't evaluated unless they're included in a query. Therefore, you should make
sure that queries don't unnecessarily retrieve them.

Another interesting aspect of computed columns is that they can sometimes be used in an index. This
is also a technique for providing the same functionality that Oracle provides with its function-based
index. Sometimes you may need to create queries which apply a function to a column in a where clause.
For example, you may write a where clause that uses the YEAR function on a date column. Once you do
this, the query optimizer will have to revert to scanning the table. However, by combining a computed
column with an index the query optimizer will use the index instead of a scan.