SQL Views with Computed Columns

[jsc0624]

Hi Guys,

Is it possible for me to create views with persisted column?

CREATE VIEW dbo.viewTable AS
SELECT
   Item1,
   Item2,
   Total AS (Item1+Item2) PERSISTED
FROM
   Table1 t1
JOIN
   Table2 t2
ON
   t1.ID = t2.ID

Also what is the proper syntax for this?

[szlamany]

What version of MS SQL SERVER are you using?

Or is this even MS SQL?

VIEW's can have "computed" columns - that's kind of one of the major features of a VIEW.

Give us more info on what your actual goal is here.

[brucevde]

Persisted is a new feature in SQL Server 2005 that if used will store the result of the computed column. Primarily used to allow indexing on computed columns.

[szlamany]

So PERSISTED takes the place of manually putting an INDEX on a computed column - which would have persisted it in the past.

Is that kind of the point?

I've used computed columns in a table once. Then eventually dropped the concept as it only made inserting rows into that table painful.

[brucevde]

Sorry, misread the docs. Persisted is required if the computed column is based on a CLR expression and you want it to be indexed.

[leinad31]

Why are you designing indexes on computed columns rather than on keys? There are other tuning methods such as checking query explain plan, disk I/O, etc, creating an index is not a one size fits all solution.