Ashley John
Ashley John

Reputation: 2453

Are computed columns in a view persisted?

CREATE VIEW [dbo].[MyView] ([ID],[VisitDate],[StartDate] ,[EndDate])
WITH SCHEMABINDING  

    AS
    SELECT     id, VisitDate,dateadd(dd,-10,VisitDate)persisted,
       dateadd(dd,10,VisitDate)persisted
    FROM         dbo.Visits

I have a non-clustered index on ID and VisitDate.I wantd to know if the computed columns StartDate and Enddate is persisted or is it calculated runtime when view is referenced

EDIT:what if i have a unique clustered index on ID and VisitDate.In that case will these columns become persisted?

Upvotes: 3

Views: 5113

Answers (2)

Alex from Jitbit
Alex from Jitbit

Reputation: 60642

Yes.

The result set of an indexed view is physically persisted in the database in a manner similar to that of typical table storage.

https://technet.microsoft.com/en-us/library/dd171921(v=sql.100).aspx

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838226

In your view, the values are calculated at runtime. The word "persisted" there is treated as the column name, not a special keyword.

If you want to create calculated columns that are persisted, you need to do so as part of the table definition, either in the CREATE TABLE statement, or as an UPDATE TABLE statement. You could also consider using an indexed view.

See the documentation for more details:

Upvotes: 4

Related Questions