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?

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.

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.

HowardW (1/24/2013)

---

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

ScottPletcher (1/24/2013)

---

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

HowardW (1/24/2013)

---

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 (1/24/2013)

---

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.

HowardW (1/24/2013)

---

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 (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.

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