create view with computed columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create view with computed columns

Author

Topic

raginbullsht
Starting Member

8 Posts

Posted - 2015-04-01 : 08:55:13

I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?

the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.

I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"

create view ViewSumReport as
select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted
from dbo.fielddata
GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-01 : 09:06:41

You can't persist a column name in a view. However, if you index it, it will be persisted.

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 13:17:22

What we do is:

Assuming a table called "MyTable" we create "MyTableView" (the naming convention can be whatever you like, but I personally think it helps if you are consistent).

The view includes ONLY the PKey columns and any computed columns (in our case we also include code lookups to save us having those JOINs in every query).

So we have:


CREATE VIEW dbo.fielddataView
AS
SELECT [V_MyPKey] = T.MyPKey,
       [V_total] = JETFOOT1+JETFOOT2+JETFOOT3
FROM dbo.fielddata AS T

and then we only ever use this in our code JOINed to the main table:


SELECT JETFOOT1,JETFOOT2,JETFOOT3,
       V_total 
FROM dbo.fielddata AS T
     JOIN dbo.fielddataView
         ON V_MyPKey = T.MyPKey

Because the JOIN is on the PKey columns it guarantees that there is one, and only one, row in the View for each row in the Table.

We use a naming convention which has unique names for all view columns (we use a nickname for the table, so "V_xxx_ColumnName" were "xxx" is unique to the table / view. This stops us having lots of "V_ID" columns floating about

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources