June 16, 2011 at 10:23 am
I want to make sure I understand what I'm reading about computed columns (note, I have not moved into the persisted columns yet as I want to break down each segment and fully understand it before moving on to the next...even when they're related as closely as these are). Here's what I understand about computed columns:
These are not true fields, but instead more of a virtual field. Each time the field is referenced, the algorithm (or expression) will be run, thus returning the latest version of the data. This acts in a similar fashion to a Trigger, but is not specifically called a Trigger.
They can only be created via the Create Table or Alter Table...Add statements.When creating the field, don't label it with a data type. Instead use the following format:
ComputedColumnName As Algorithm (i.e. - (Field1 + Field2) * Field3)
Computed columns can be used in Select, Where, and Order By clauses, but cannot use Insert or Update directly into the field.
The algorithm (or expression) can contain a non-computed field name, constants, functions (i.e. - GetDate(), etc. - this includes SQL functions as well as user-defined functions...I have read this, and I believe this references the functions created similarly to stored procedures...do I have that right, or is this something else that I'm missing). Also any combination of the above are allowed.
The algorithm (or expression) cannot contain another computed column, nor a sub-query (even a basic Select statement).
The computed column cannot reference another computed column on the same table.
I have read this next line many times over, but I'm still not clear about what is being stated here. Can anyone clear this up a bit?
You are reference a computed column in another table by using a user defined function in the expression of the calculated column.
Quote from: http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx
Computed columns cannot be indexed without moving to the Persisted version...which I'm not getting into just yet.
Do I have a general understanding, or am I way off? Also, will you please add your comments on the above questions?
Sorry if I'm going a touch overboard with this...I just like to get fully involved so I know not only what it does, but why it does it.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 16, 2011 at 11:28 am
jarid.lawson (6/16/2011)
Here's what I understand about computed columns:
Pretty much, yes
I have read this next line many times over, but I'm still not clear about what is being stated here. Can anyone clear this up a bit?
You are reference a computed column in another table by using a user defined function in the expression of the calculated column.
Quote from: http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx
A computed column can only reference columns in the same table. So if you want a computed column that uses columns from another table, you need a user-defined function.
Example from a blog post of mine:
CREATE TABLE Individuals (
Generation SMALLINT,
IndividualID SMALLINT,
CONSTRAINT pk_Individuals PRIMARY KEY CLUSTERED (Generation, IndividualID)
);
CREATE TABLE IndividualDetails (
Generation SMALLINT,
IndividualID SMALLINT,
ObjectNumber TINYINT,
Quantity TINYINT,
CONSTRAINT pk_IndividualDetails PRIMARY KEY CLUSTERED (Generation, IndividualID, ObjectNumber),
CONSTRAINT fk_IndividualDetails_Individuals FOREIGN KEY (Generation, IndividualID) REFERENCES dbo.Individuals (Generation, IndividualID)
);
CREATE FUNCTION dbo.Fitness(@Generation SMALLINT, @Individual SMALLINT)
RETURNS NUMERIC(6,2)
AS
BEGIN
DECLARE @TotalValue NUMERIC(6,2) = 0;
SELECT @TotalValue = SUM(id.Quantity*os.Value)
FROM dbo.IndividualDetails id
INNER JOIN dbo.ObjectStatistics os ON id.ObjectNumber = os.ObjectNumber
WHERE Generation = @Generation AND IndividualID = @Individual;
RETURN @TotalValue;
END;
GO
ALTER TABLE dbo.Individuals
ADD Fitness AS dbo.Fitness(Generation, IndividualID); -- a computed column that references columns in another table.
GO
Computed columns cannot be indexed without moving to the Persisted version...which I'm not getting into just yet.
That's wrong. They can be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2011 at 11:43 am
Outstanding!!! Thank you for the review. 😀
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply