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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2004-08-28 : 18:48:59
|
| Ok, so I got all lazy and ran the index tuning wizard on a slow-running query rather than looking at the query plan myself. Well, you know what curiosity did to the cat.Much to my surprise, the wizard recommended an indexed view. It was the first time I'd seen that. Sure, says I, let's try it.BOOM! Of course, every query in the entire app that the optimizer decided to use the view for was now running wihe the wrong ANSI_NULLS, ARITHABORT, and QUOTED_IDENTIFIERS options. Effectively, I exploded our entire web app for a minute or two.I understand, in principle, what those options do and why indexed views need certain options. However, I'm mystified about whether there is a "correct" set of options, possibly the one indexed views wan, that I can use throughout my app. On the app side, there are two functions that connect to the database, so setting options for the entire app is easy.However, do I want to do that? If so, do I need to edit all of my stored procedures to set them to being created with the right options?In short, I've read a lot of the documentation on this but I need practical advice.Thanks-b |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-08-30 : 13:15:25
|
| Am I getting no love here because it's one of those bloody obvious things that I should just know (or find spelled out in big print on BOL)? Or is everyone else as confused as I am about this?Thanks-b |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-30 : 20:42:00
|
There is a very good set of things which should be set globally in SQL Server. These will allow you to use indexed views, computed columns, and all kinds of good features in SQL Server 2000. From BOL:Requirements for the CREATE INDEX StatementThe first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX.The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements: The user executing the CREATE INDEX statement must be the view owner.These SET options must be set to ON when the CREATE INDEX statement is executed: ANSI_NULLSANSI_PADDINGANSI_WARNINGSARITHABORTCONCAT_NULL_YIELDS_NULLQUOTED_IDENTIFIERS The NUMERIC_ROUNDABORT option must be set to OFF.The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-30 : 21:13:08
|
| Derrick,Ever since the 'fun with binary' postings we had a while back, every thread that I read in which you post is all way out of whack. It scrolls 3 or 4 pages to the right.i noticed that your signature is back to normal, so i wonder what the heck is going on?Ooops, sorry for the left turn right in the middle of a thread (that never happens here does it?)-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-30 : 23:40:42
|
I noticed that also. Don't know what's causing it though. Graz is going to fire me though if I keep causing weird thing to happen on his website.  MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-08-31 : 12:50:13
|
| I think you have a really long line inside a [ code] tag. [ code] gets translated to HTML <pre>, which doesn't allow word wrapping.I've read this from BOL, but I'm still confused. When you say "set globally", do you mean "set for each connection"? That is, should I ensure that those 6/1 things are set on/off for every connection?If so, that's easy enough, but -- if I do that, will it break all of my existing SP's that were created with different connection options? If so, is there a bulk way to update the options for exisitng SP's? Also, is there anything else I should know before doing that? I'm a little scarred from my experience the other night Thanks-b |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-08-31 : 19:37:20
|
| For future reference, changing an ASP web app's connection settings to these does not seem to cause any problems or require any DB-side work.Cheers-b |
|
|
|
|
|
|
|
|
Indexed views, connection options, argh