Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed column with/with out PERSISTED


Computed column with/with out PERSISTED

Author
Message
SQL*
SQL*
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1726
Hi All,

While reading about Computed column if found that if we mentioned PERSISTED Key word while creating/altering table then we can create index. But i'm able to create index with out that key word also.


By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise.

What i was thinking on difference between Computed column/Persisted computed column as if we create index on computed column then we can say that computed column as persisted column.

What is the need of computed column if we can do the same thing in the query/application?
Can anybody give me such scenario where we must use this Computed column only?

:-)
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
SQL* (1/23/2013)
What is the need of computed column if we can do the same thing in the query/application?Can anybody give me such scenario where we must use this Computed column only?

Lets take example of TAX table where

following are the column

Basic_salary
HRA_Exemption
SAving_80C
saving_80CCC
calulated_tax (computed column)

So whenever "Basic_salary ,HRA_Exemption,SAving_80C,saving_80CCC" gets populated the formula attached with caluated_tax column , will populated this column. NO application or query intervention required

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
SQL*
SQL*
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1726
:-)
I got the importance of the Computed column, but what about other questions? PERSISTED/Creating Index on Calculated column.

:-)
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
SQL* (1/23/2013)
Hi All,

While reading about Computed column if found that if we mentioned PERSISTED Key word while creating/altering table then we can create index. But i'm able to create index with out that key word also.


By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise.


There's no need to make the column persisted prior to indexing it.

I'm assuming you're referring to the text here. It reads like it's been copied out of context from somewhere else and the real meaning has been lost as a result.

I suspect it's really referring to computed columns that are deterministic, but imprecise (usually based on CLR functions), but I can see why you're confused.

This documentation has a more complete explanation of the imprecise computed column requirements :

http://msdn.microsoft.com/en-us/library/ms189292.aspx#BKMK_persisted

You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise.

ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
HowardW (1/24/2013)
SQL* (1/23/2013)
Hi All,

While reading about Computed column if found that if we mentioned PERSISTED Key word while creating/altering table then we can create index. But i'm able to create index with out that key word also.


By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise.


There's no need to make the column persisted prior to indexing it.


That's not necessarily always true. You might persist a column if its requires significant overhead to generate the value.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
ScottPletcher (1/24/2013)
HowardW (1/24/2013)

There's no need to make the column persisted prior to indexing it.


That's not necessarily always true. You might persist a column if its requires significant overhead to generate the value.


I think you've misunderstood my comment. You need to read the whole sentence, including the bit I've highlighted. Whether it's persisted via an index or in the column definition, it's still only calculated on modification.
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
HowardW (1/24/2013)
ScottPletcher (1/24/2013)
HowardW (1/24/2013)

There's no need to make the column persisted prior to indexing it.


That's not necessarily always true. You might persist a column if its requires significant overhead to generate the value.


I think you've misunderstood my comment. You need to read the whole sentence, including the bit I've highlighted. Whether it's persisted via an index or in the column definition, it's still only calculated on modification.



I don't think you're thinking clearly about this. If it's not persisted, it has to be computed every time it's SELECTed or otherwise referenced. If that computation is a lot of overhead, it can be worthwhile to instead persist the computed value, especially if it's referenced frequently.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
ScottPletcher (1/24/2013)
HowardW (1/24/2013)
ScottPletcher (1/24/2013)
HowardW (1/24/2013)

There's no need to make the column persisted prior to indexing it.


That's not necessarily always true. You might persist a column if its requires significant overhead to generate the value.


I think you've misunderstood my comment. You need to read the whole sentence, including the bit I've highlighted. Whether it's persisted via an index or in the column definition, it's still only calculated on modification.



I don't think you're thinking clearly about this. If it's not persisted, it has to be computed every time it's SELECTed or otherwise referenced. If that computation is a lot of overhead, it can be worthwhile to instead persist the computed value, especially if it's referenced frequently.


Nope, I think you're not thinking clearly. How an earth would an index be able to be used if the key column was calculated on the fly during a select? Indexing a computed column persists it regardless of whether the column is marked as persisted in its definition.
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
HowardW (1/24/2013)
ScottPletcher (1/24/2013)
HowardW (1/24/2013)
ScottPletcher (1/24/2013)
HowardW (1/24/2013)

There's no need to make the column persisted prior to indexing it.


That's not necessarily always true. You might persist a column if its requires significant overhead to generate the value.


I think you've misunderstood my comment. You need to read the whole sentence, including the bit I've highlighted. Whether it's persisted via an index or in the column definition, it's still only calculated on modification.



I don't think you're thinking clearly about this. If it's not persisted, it has to be computed every time it's SELECTed or otherwise referenced. If that computation is a lot of overhead, it can be worthwhile to instead persist the computed value, especially if it's referenced frequently.


Nope, I think you're not thinking clearly. How an earth would an index be able to be used if the key column was calculated on the fly during a select? Indexing a computed column persists it regardless of whether the column is marked as persisted in its definition.



Of course indexing requires persisting. I thought everyone was clear on that.

But you claimed, in your first quote above, there was no (never a) need to persist except to index. That's simply not true. Even if a computed column is not indexed in any way, there are times when it's still worthwhile to persist it to avoid re-computing it.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
ScottPletcher (1/24/2013)


Of course indexing requires persisting. I thought everyone was clear on that.

But you claimed, in your first quote above, there was no (never a) need to persist except to index. That's simply not true. Even if a computed column is not indexed in any way, there are times when it's still worthwhile to persist it to avoid re-computing it.


Right, OK, I see where the confusion is now, even if I don't know where you think I said there's never a need to persist a computed column, except to index it. I certainly didn't claim that, hence why I pointed out that you misunderstood my comment. If that was what I wanted to say, I would have said:

"There's no need to make the column persisted except to index it"

But what I said (and highlighted again after your comment) was:


There's no need to make the column persisted prior to indexing it.


As in, there's no requirement to persist the column before indexing it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search