question

askmlx121 avatar image
askmlx121 asked

view have Schemabinding...Can we Create unique Clustered indexed on view ?

We have a table A main column of accountid uniqueidentifier not null,hostid int not null it has 88000 records and other table B have 100 Records. Now I created indexed view with schema binding using UNION operator. It looks like ---------- create view viewname with schema binding as select tablenameA.Column1,tablenameA.Column2,tablenameA.Column8, tablenameB.Column1,tablenameB.Column2 from dbo.tablenameA JOIN dbo.tablenameB ON tablenameA.column1=tablenameB.column1 where some condition union select tablenameA.Column1,tablenameA.Column2,tablenameA.Column8, tablenameB.Column1,tablenameB.Column2 from dbo.tablenameA JOIN dbo.tablenameB ON tablenameA.column1=tablenameB.column1 where some condition ---------- It sucessfully created as Indexed view of schema binding. But My problem is when i try to create unique clustered index on this particular view, it shows some error like below **Cannot create index on view 'databasename.dbo.viewname'. It contains text,ntext,image or xml columns (Microsoft SQL Server, Error:1942).** I want to implement index on Accountid,it does not have text,ntext,image,xml. It has unique,not nullable. So which is problem? Can we could not able to create index on view those using UNION OPERATOR? Quick Suggestions are HIghly appriciated............... I immensely thanking those solve this issue. It sucessfully created with schema ![alt text][1] [1]: /storage/temp/256-could+not+create+index+on+view.jpg
unionindexed-view
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
As per [BOL][1]: > Requirements for the CREATE INDEX Statement > ... > * The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement. [1]: http://msdn.microsoft.com/en-us/library/ms191432(v=sql.105).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
The problem is not with the Accountid column, there may be some other field in your view with one of the data types mentioned in the error. As it states, you cannot use text,ntext,image or xml columns for **indexing the view**.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Mr. Usman, No i have tried with some other non nullable column of int datatype also....it showed same error. DO UNION OPERATOR HAVE THE PROBLEMS when it included on view?
0 Likes 0 ·
@askmlx121 I am saying that one of the columns in your VIEW may have the data type that cannot be part of an indexed view. Yes, you are right you cannot use UNION as well for an indexed view.
0 Likes 0 ·
askmlx121 avatar image
askmlx121 answered
Hi kev Riley, I am your lovable fan....could suggest me plz? i am your bet ...is nt.......? thank you for your Immediate response..for Mr.usman & Mr kev Riley sir,,,,,,,,, by Ashok
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
UNION in your views won't allow your view to be indexed!, you view should satisfy all the [requirement mentioned here][1] to create indexed view. [1]: http://msdn.microsoft.com/en-us/library/ms191432.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.