Hi All,
I have a SQL database which has many views and needs indexing. Unfortunately SQL server does not let me add indexes with schema bind error. There are app. 50 views created on the database and I don't want to delete and re-create them one by one.
Is there an easier way to convert regular views to schemabind views to save time and effort?
Many thanks
telmessos
Alas, there is not. However, you can use SSMS to generate the alter statements and then add the "WITH SCHEMABINDING" attribute fairly quickly. As long as there's no change to the structure of the view, you should be pretty safe.
Just a few caveats though:
1. Once you do schema binding, you can't drop the underlying table(s), or alter them in a way that will affect the view definition. See MSSQL Books OnLine "ALTER VIEW" for more information.
2. The first index you put on a schema-bound view MUST BE a unique clustered index. Subsequent indices don't need to be.
3. All the referenced tables have to be in the same database.
Sorry there's not better news...good luck!
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.