0

I have this view, would like to know how to make "unique index view"

ALTER VIEW vwCF
as  
SELECT
cf.CashFlowID
,ftt.FTSName
,cf.FTSTypeID
,ftt.SortOrder AS ftsSortOrder
,cf.SecurityTypeID
,st.SecurityTypeName
,st.SortOrder AS stSortOrder
,cf.IssuanceDate
,cf.Issuance
,cf.Principal
,cf.Interest
,cf.FlowAmounts
,cf.Net
 FROM dbo.CashFlow AS cf
 INNER JOIN dbo.FinancialTimesType AS ftt ON cf.FTSTypeID = ftt.FTSTypeID
 INNER JOIN dbo.SecurityType AS st ON cf.SecurityTypeID = st.SecurityTypeID
 Go

How to do create unique index view and go from here? Thanks

user3018092
  • 1
  • 1
  • If the DB is SQL SERVER Check [this](http://stackoverflow.com/questions/8506487/cannot-create-index-on-view-view-table-name-because-the-view-is-not-schema-bou) and [this](http://stackoverflow.com/questions/9199418/sql-server-indexed-views). – user2989408 Jan 16 '14 at 18:27

1 Answers1

0

Assuming that this is sql server, you can use the code below. You'll need to replace [id field] at the bottom with the field(s) you want to use.

DROP VIEW vwCF
GO
CREATE VIEW vwCF WITH SCHEMABINDING
AS
cf.CashFlowID
,ftt.FTSName
,cf.FTSTypeID
,ftt.SortOrder AS ftsSortOrder
,cf.SecurityTypeID
,st.SecurityTypeName
,st.SortOrder AS stSortOrder
,cf.IssuanceDate
,cf.Issuance
,cf.Principal
,cf.Interest
,cf.FlowAmounts
,cf.Net
FROM dbo.CashFlow AS cf
INNER JOIN dbo.FinancialTimesType AS ftt 
    ON cf.FTSTypeID = ftt.FTSTypeID
INNER JOIN dbo.SecurityType AS st 
    ON cf.SecurityTypeID = st.SecurityTypeID;
CREATE UNIQUE CLUSTERED INDEX idx_vwCf ON vwCF ([id field]);

[More on indexed views] :http://msdn.microsoft.com/en-us/library/ms191432.aspx

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
  • Getting two errors: Msg 111, Level 15, State 1, Line 2 'CREATE VIEW' must be the first statement in a query batch. Msg 319, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'with'. If this statement is a common table expression....etc. – user3018092 Jan 17 '14 at 13:52
  • I forgot the "GO" statement after the Drop View. I've edited my answer. – RubberDuck Jan 17 '14 at 16:40
  • Please mark as the correct answer if this solved your problem. – RubberDuck Jan 21 '14 at 16:30