May 12, 2005 at 3:56 am
Hello,
I would like to create an indexed view, but I get an error. Here is my query and the error I get:
CREATE view dbo.AvailableProduct1WDates WITH SCHEMABINDING
As SELECT distinct p.propertyId, p.propertyTypeId, p.commercialName, cl.cityName, al.areaName, ph.photoPath, pl.propertyDesc, p.classification, p.availabilityType, p.discount, list.capacity, list.star, l.descriptionAndPhotos, l.ratesLink, l.newSearch, list.rooms, list.rates, p.numOfRooms, p.overallCapacity, pr.rate, pr.period, eq.equipmentId, eq.equipmentValue, eq.elementId as equip_elementId, ser.serviceId, ser.elementId as serv_elementId
from dbo.VM_PHOTOS as ph, dbo.VM_ROOM as r, dbo.VM_AREA_LOCAL as al, dbo.VM_PROPERTY as p, dbo.VM_PROPERTY_LOCAL as pl, dbo.VM_AVAILABILITY as av, dbo.VM_PRICING as pr, dbo.VM_EQUIPMENTS_PROPERTY as eq, dbo.VM_SERVICES_PROPERTY as ser, dbo.VM_LISTLABELS as list, dbo.VM_CITY_LOCAL as cl, dbo.VM_SEASON as sea, dbo.VM_PERIOD as per, dbo.VM_PRODUCT as pro, dbo.VM_PROPERTY_TYPE as pt, dbo.VM_PROPERTY_TYPE_LOCAL as ptl, dbo.VM_LINKS as l
WHERE av.propertyId = p.propertyId AND pro.productId = pt.productId AND pt.propertyTypeId = p.propertyTypeId AND ptl.cultureId= 1 AND pt.productId=1 AND p.propertyTypeId= pt.propertyTypeId AND ptl.propertyTypeId= pt.propertyTypeId AND av.startAvailabilityDate <= '05/12/2005' AND av.endAvailabilityDate>= '05/19/2005' AND pr.propertyId = p.propertyId AND p.propertyId = sea.propertyId AND sea.seasonId = per.seasonId AND pr.seasonId = per.seasonId AND sea.minimumStay <=7 AND per.startPeriod <='05/12/2005' AND per.endPeriod >= '05/19/2005' AND pr.period =7 AND pl.propertyId = p.propertyId AND p.propertyId = ph.propertyId and ph.photoType = '1' AND pl.cultureId=1 AND p.cityId = cl.cityId AND al.areaId=p.areaId AND al.cultureId=1 AND cl.cultureId=1 AND p.overallCapacity >= 1 AND p.classification >=1 AND p.numOfRooms >= 1 AND list.cultureId = 1 AND l.cultureId = 1
GROUP BY p.propertyId, p.commercialName, al.areaName, cl.cityName, p.overallCapacity, p.availabilityType, p.discount, ph.photoPath, pl.propertyDesc, p.numOfRooms, p.classification, list.capacity, list.star, list.rooms, list.rates, p.propertyTypeId, pr.rate, l.descriptionAndPhotos, l.ratesLink, l.newSearch, pr.period, eq.equipmentId, eq.equipmentValue, ser.serviceId, eq.elementId, ser.elementId
GO
CREATE UNIQUE CLUSTERED INDEX propertyIdInd ON AvailableProduct1WDates (propertyId)
here is the error I got:
Cannot index the view 'VM.dbo.AvailableProduct1WDates'. It contains one or more disallowed constructs.
Can anyone help me with that?
Thanks you beforehand
Zineb
May 12, 2005 at 6:28 am
From SQL Team
There are several requirements that you must take into consideration when using Indexed views.
1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
1. TOP
2. Text, ntext or image columns
3. DISTINCT
4. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
5. SUM on a nullable expression
6. A derived table
7. Rowset function
8. Another view
9. UNION
10. Subqueries, outer joins, self joins
11. Full-text predicates like CONTAIN or FREETEXT
12. COMPUTE or COMPUTE BY
13. Cannot include order by in view definition
May 12, 2005 at 9:43 am
Thank you very much for those information.
Can you give me an example how to include COUNT_BIG(*) in my query?
Thank you beforehand
Zineb
May 12, 2005 at 9:45 am
Select count_big(*) as CountAsBigInt from dbo.SysObjects
May 13, 2005 at 9:28 am
I think you are referencing lots of tables in your view. So the underlying data may be more volatile than you think.
Another approach you may want to consider is materializing your view into a table...
i.e.
do a 'select into' to populate a table initially, and whenever it needs to be refreshed, truncate this table, and reinsert the rows. You can index this table easily then so it can be used easily in other queries...
May 13, 2005 at 12:42 pm
Thank you very much Jonathan.
It's a good idea, I'll try it!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply