Hugo Kornelis (10/8/2012)
I really liked the question. Unlike Tom, I even liked the inclusion of all the settings, and I don't think it's a red herring. This question is about knowing all the requirements for indexing a view, and the only way to test that is to use questions that either get all the requirements right, or all except one.And if this results in people reading Books Online to check all the required SET options, then I would argue that this is a good thing. (Not that I think that anyone should know these requirements by head - but people should know the requirements exist, and know where to find them documented).
OK, maybe calling the settings a red herring is a bit excessive. But I think of them that way, for the following reason
(i) these are the server defaults for those settings
(ii) I tend to do unit testing in SSMS, where my connection automatically uses the server defaults, so I don't need to make these settings
(iii) To get code to work in a production or system test environment without making alterations between the unit test environment and that environment, I have to ensure that the connection established in that environment has all these settings before it gets to execute any of the tested code; ideally this should be done globally, not piecemeal, so it should be done at server installation time by running the script
exec sp_configure 'user_options', 5496
reconfigure
-- worked in sql 2000, sql 2008 R2, don't know about 2005, 2008
(iv) so no-one working on a system I control should ever need to set any of these options using the T-SQL SET command.
(v) before I learnt about this method I had a rule that all DDL scipts should contain the set statements at the front, so they didn't need to be included as and where necessary, they were automatic. after I learnt it and applied it, the rule changed to none of those values should ever be touched by a set statement.
(vi) I'm looking forward to the day when most of these options will be impossible to change from the default values (most are already depreciated) and the rest will have the server defaults as the defaults for all connections.
Tom