April 28, 2005 at 3:48 am
My application did some ALTER TABLE clauses to a table created with ANSI_PADDING off. Because the app was very slow then (270.000 rows in the table and some joins to other tables to complete information) I put my Select clause into the Index optimizer. It created a view with schemabinding what was very effective, the query speeded up from 58 s to 2 s! But when I try to insert or update data in this table, there's an error that update or insert is not possible because of wrong ANSI-settings.
I recreated the table with all columns ANSO_PADDING on. That step said that schemabinding of the view was broken. Insert and update worked well. When I recreate the view with schemabinding, the error occurs again.
Unfortunately this happens only on the customer's server, not on my own, if the view is schemabinding or not.
Is there any idea that could help me. Or is there something general I have to learan about?
Thanks
Kathrin
May 2, 2005 at 8:00 am
This was removed by the editor as SPAM
September 27, 2005 at 5:53 pm
If any of these options are not set to the required values, INSERT, UPDATE, and DELETE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as though the indexes on computed columns or on the views do not exist.
See the following link for details...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_40c4.asp
A.J.
DBA with an attitude
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply