Cannot create index on view with derived table

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

SQL Server 2008 Forums

Transact-SQL (2008)

Cannot create index on view with derived table

Author

Topic

Wheel
Starting Member

1 Post

Posted - 2011-07-13 : 10:29:45

Dear,

i want to index this view so i can use it in my replication butr i cannot create an unique clusterd index because of the left joins.
How can i solve this?

CREATE VIEW [dbo].[vwReplEmptyProvisionsHomeTerminal3] with schemabinding
AS
(
select Berthing_id,
Vessel_id
from
(SELECT dbo.Berthings.Berthing_id,

dbo.Berthings.Vessel_id,

dbo.Vessels.VesselCode,
dbo.Vessels.VesselName,
dbo.Vessels.LloydsRegistryNbr,
dbo.Vessels.CallSign,

dbo.Countries.FlagName,

dbo.Vessels.InService as VesselsInService,

dbo.Berthings.voyage,
dbo.Berthings.plannerberth,

dbo.Berthings.PlannerSpecialArrival,
dbo.Berthings.PlannerSpecialDeparture,

dbo.Berthings.Service_id,

coalesce(dbo.Services_Ports.ServiceCode, dbo.Services.BerthingCode) as ServiceCode,
dbo.Services.ServiceName,
dbo.Services.InService as ServicesInService,

dbo.Berthings.PlannerArrivalPilot,
dbo.Berthings.PlannerArrivalBerth,

dbo.EmptyProvisions.DischargedEmpties20DV,
dbo.EmptyProvisions.DischargedEmpties20OT,
dbo.EmptyProvisions.DischargedEmpties20FL,
dbo.EmptyProvisions.DischargedEmpties20RF,
dbo.EmptyProvisions.DischargedEmpties40DV,
dbo.EmptyProvisions.DischargedEmpties40OT,
dbo.EmptyProvisions.DischargedEmpties40FL,
dbo.EmptyProvisions.DischargedEmpties40HRF,
dbo.EmptyProvisions.DischargedEmpties40HC,

dbo.Berthings.InService as BerthingsInService,
dbo.Berthings.InsTime,
dbo.Berthings.UpdTime,
dbo.Berthings.DelTime
FROM
dbo.Berthings
LEFT JOIN dbo.EmptyProvisions ON (dbo.Berthings.Berthing_id = dbo.EmptyProvisions.Berthing_id)
LEFT JOIN dbo.Vessels ON (dbo.Berthings.Vessel_id = dbo.Vessels.Vessel_id)
LEFT JOIN dbo.Countries ON (dbo.Vessels.Country_id = dbo.Countries.Country_id)
LEFT JOIN dbo.Services ON (dbo.Berthings.Service_id = dbo.Services.Service_id)
LEFT JOIN dbo.Services_Ports ON (dbo.Berthings.Service_id = dbo.Services_Ports.Service_id) and
(dbo.Berthings.CurrentPort_id = dbo.Services_Ports.Port_id)
WHERE dbo.Berthings.CurrentPort_id = 743) as a --only antwerp
)

CREATE UNIQUE CLUSTERED INDEX [IX_vwReplEmptyProvisionsHomeTerminal2_index_Berthing_id]
ON [dbo].[vwReplEmptyProvisionsHomeTerminal3] ([Berthing_id])

I get error

Msg 10109, Level 16, State 1, Line 1
Cannot create index on view "CPDS_PROD.dbo.vwReplEmptyProvisionsHomeTerminal3" because it references derived table "a" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-13 : 11:25:19

I never replicate views. I replicate the tables referenced by them, and create the same view on the subscriber.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources