Presents your  
SQL SERVER E-NEWSLETTER for November 11, 2003 
 
<-------------------------------------------> 
  
DECLARING A COMPUTED COLUMN WITH A CREATE TABLE STATEMENT   
  
Dating back to relational database theory founder E. F. Codd, a general 
principle has emerged: Never store what can be calculated. This principle 
sidesteps the question, "Where should we calculate this calculated 
result?" The default answer is, "In the front-end application."   
  
Imagine a table containing the columns SubTotal and TaxRate. According 
to the principle, the "columns" TaxAmount and Total should be virtual. 
Therefore, create them as part of a view or stored procedure; or, 
alternatively, leave it up to the front-end application to derive these
and present them to the user.   
  
The alternative to this principle is called computed columns. With this 
expression, you can declare a computed column as part of your CREATE 
TABLE statement however that might be issued to the database (whether
it's via a GUI or directly as a DDL instruction).   
  
Let's imagine a database that contains house painting estimates. 
Ignoring details such as the number of windows and doors in a room (and
assuming that all walls will be painted the same color), two questions remain: 
Do we paint the ceiling the same color (typically, the answer would be 
"no"), and how many coats of paint will we use (typically, the answer
would be "two")?   
  
Without computed columns, we'd defer these questions and the resulting 
calculations to the front-end application. With computed columns, we 
can embed the formula in the database, creating a virtual column that is 
available for use by any front end.   
  
You can create the table with this statement:   
  
CREATE TABLE [TestComputedColumns] (   
    [PK] [int] IDENTITY (1, 1) NOT NULL ,   
    [Length] [int] NOT NULL ,   
    [Width] [int] NOT NULL ,   
    [Height] [int] NOT NULL ,   
    [Coats] [int] NOT NULL CONSTRAINT [DF_TestComputedColumns_Coats] 
DEFAULT   
 (2),   
    [IncludeCeiling] [bit] NOT NULL CONSTRAINT   
 [DF_TestComputedColumns_IncludeCeiling] DEFAULT (0),   
    [Area] AS ((2 * ([Height] * [Length] + [Height] * [Width]) + 
[Length] *   
 [Width] * [IncludeCeiling]) * [Coats]),   
    CONSTRAINT [PK_TestComputedColumns] PRIMARY KEY  CLUSTERED   
    (   
        [PK]   
    )  ON [PRIMARY]   
) ON [PRIMARY]   
GO   
  
Using your front end of choice, insert a few rows to see how this works.

For example, using Access 2000 +, create an Access Data Project (ADP) 
that points to your database, select the table, and create an AutoForm. 
Enter a row or two and then page back through your entries; you'll see
that the computed column has the correct values.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------