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 |
|
VonC
Starting Member
6 Posts |
 Posted - 2009-03-24 : 06:09:28
|
| Hi, I created an View in SQL2008 Dev (DB is set to 2005 v9) with schemabinding and then added (using Query window t-sql) a clustered index to it fine. It was a composite unique clustered index, so I checked that I couldn't add a duplicate record in the base table and in the table it correctly gave me the new view's index's error for dupe record - excellent!BUT, I then added a simple filter (just a where year_id = 9) to this View, and SSMS gave the error:"Its likely that your modifications to <Viewname> will result in a view that cannot be indexed".It advised cancelling otherwise it would only re-establish the index if it could be accomodated. This is a bit vague, and most importantly, using a Where on an Indexed View is not ruled out in the BOL requisites - it would seem to be pretty basic functionality. I feel led up the garden path a bit.Anyone else had or verify this problem / ideas appreciated? Cheers, Phil. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-03-24 : 06:36:46
|
| Did you try to create the view anyway? This is only a warning/informational message and if you are sure your where clause is fine, it can be ignored. |
 |
|
|
VonC
Starting Member
6 Posts |
Posted - 2009-03-24 : 07:02:58
|
| Thanks for responding Rick - you are right, I applied it anyway (even though "... it is likely that...") and as I can't see a way to verify if the index is still fully applied (I guess it would have to drop the whole index if it couldn't apply it? So I could verify through the system views) I tested by attempting another duplicate record. It still applied the clustered index error, so it still held the index, phew!What a hassle though - surely MS could be more specific and provide a proper check. In this case, I can verify by testing with violating records, but that's not always the case - most of the time an index will be applied to the view for performance, not integrity, so it's difficult to confidently test and verify.Hopefully MS will attend to this, will see if further issues with indexed views. Thanks again, Phil. |
|
|
|
|
|
|
|
|
Indexed View fails with basic Where filter!