Use CREATETABLE statements to declare and calculate data Columns

Back in the era of E. F. Codd, the founder of relational database theory, a general principle has emerged: never store the content that can be computed ). This principle avoids the question: "Where should we calculate the calculated result ?" The default answer is: "In the front-end application ." Imagine 1.

Back in the era of E. F. Codd, the founder of relational database theory, a general principle has emerged: never store the content that can be computed ). This principle avoids the question: "Where should we calculate the calculated result ?" The default answer is: "In the front-end application ." Imagine 1.

Back to relational DataIn the era of E. F. Codd, the creator of library theory, a general principle has emerged: never save Computing). This principle avoids the question: "Where should we be? ComputingThis already ComputingWhat are the results ?" The default answer is: "In the front-end application ."

Imagine a table containing the SubTotal and TaxRate columns. According to this principle, the two "columns" TaxAmount and Total should be virtual. Therefore, you need to create them as part of viewing tables or stored procedures. Alternatively, you can use a different method to let the front-end applications generate them and present them to users.

A replacement method for this principle is Computing DataColumn (computed column ). With this representation, you can Computing DataColumn StatementCREATE TABLE StatementAlthough this may be released DataLibrary (whether through the graphical user interface or directly DataDefinition Language commands ).

Now let's assume there is DataLibrary, which contains an estimate of the house paint work. Ignoring details such as the number of doors and windows in the room (and assuming that all the walls are painted in the same color) brings two problems: do we need to paint the ceiling in the same color (generally, the answer is "no"); how many layers do we need to paint (generally, the answer is "two layers ")?

If no Computing DataColumn, we have no better way to deal with these problems, so that only ComputingPush to the front-end application. And now Computing DataColumn, We can embed the equation DataLibrary to create a virtual DataColumn for any front-end.

You can use the following StatementTo create a table:

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

You can use the frontend to insert some DataOkay. Let's see how it works. For example, use Access 2000 + to create DataDatabase Access DataAccess Data Project (ADP), select a table, and create an AutoForm. Enter one or two DataLine, and then return to the front with your input; you will see Computing DataThe column gets the correct value.