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.
| Author |
Topic |
|
naren.suggul
Starting Member
3 Posts |
 Posted - 2008-03-14 : 04:14:31
|
| when I tried to execute a create index on existing view, I got a message Cannot index the view 'pubs.dbo.V_test_table'. It contains one or more disallowed constructs.please tell what disallowed constructs mean and how to debug |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-03-14 : 04:18:05
|
post your create view statement here.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
naren.suggul
Starting Member
3 Posts |
Posted - 2008-03-14 : 04:41:25
|
| CREATE VIEW V_test_table WITH SCHEMABINDING AS SELECT DISTINCT C.c1 [CUSTOMER_ID],A.c2,A.c3, C.c4,'value' AS c5 FROM (SELECT c6,c2,c3 FROM DBO.t1 GROUP BY c6,c2,c3) A JOIN (SELECT c6,c7 FROM DBO.t2 GROUP BY c6,c7) B ON A.c6 = B.c6 JOIN DBO.t3 C ON C.c8 = B.c7 WHERE c9 = 'P' |
|
|
|
rolandsantos
Starting Member
24 Posts |
Posted - 2008-03-14 : 05:18:18
|
| Some criteria when using indexed views are:SELECT * statements, SELECT DISTINCT, SELECT TOP, SELECT COUNT(*), Subqueries, unions, outer joins, self joins are not allowed |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-14 : 05:32:27
|
Neither are derived tables.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rolandsantos
Starting Member
24 Posts |
Posted - 2008-03-14 : 05:50:30
|
| BOL states that SELECT DISTINCT is not allowed as i mentioned earlier. |
|
|
|
|
|
|
|
|
error creating indexed views