View - 8
-
Comments posted to this topic are about the item View - 8
-
Good and Easy Question +1.
Thanks;-)
-
It's a question with a correct answer, no scope for argument for that (as I have come to expect from Ron's questions, which seem to be always accurate - unlike mine, or indeed pretty well anyone elses), so in that respect it's a good question.
But all those settings up front are just a red herring - completely irrelevant to the question. It's not a trick question, because it's extremely unlikely that anyone thinks that those settings could have any effect on the answer. Nevertheless, I think that that stuff detracts from the quality of the question; it might leave someone wondering if there were some setting (not one of those listed) that could have affected the answer, which would be unfortunate.
But never mind, maybe I should like it anyway, because there's nothing actually wrong with it and I got it right. :laugh:
BTW, it's another Monday question released rather early, of course. Is "dbcc timewarp" somehow involved? Should I be able to answer Monday's question in the small hours of the preceeding Sunday?
Tom
-
L' Eomot Inversé
It's a question with a correct answer, no scope for argument for that (as I have come to expect from Ron's questions, which seem to be always accurate - unlike mine, or indeed pretty well anyone elses), so in that respect it's a good question.
And I thank you for the compliment ... so often the comments are far, far away from being complimentary. Let us see how it developes as more answer and comment.
-
good question..
nice and easy ..
Thanks
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
L' Eomot Inversé (10/6/2012)
It's a question with a correct answer, no scope for argument for that (as I have come to expect from Ron's questions, which seem to be always accurate - unlike mine, or indeed pretty well anyone elses), so in that respect it's a good question.I must agree in accord. Many a times when I post a question, either there is some anomaly in the question or there is something wrong with the way I explain the answer of that question.
Ron's questions are often to the point and accurate. Keep it up. It's a learning for people like me 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
L' Eomot Inversé (10/6/2012)
But all those settings up front are just a red herring - completely irrelevant to the question. It's not a trick question, because it's extremely unlikely that anyone thinks that those settings could have any effect on the answer. Nevertheless, I think that that stuff detracts from the quality of the question; it might leave someone wondering if there were some setting (not one of those listed) that could have affected the answer, which would be unfortunate.Those couple of SET options seems to be red-herring, but they are pretty much in-line with BOL text. Here is the excerpt taken from BOL (link mentioned by Ron)
The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:
1. Verify the SET options are correct for all existing tables that will be referenced in the view.
2. Verify the SET options for the session are set correctly before creating any new tables and the view.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Very good + clear question. thanks... nice way to start the week.
-
Great question Ron, thanks!
Another thing that might lead to the create index statement to fail:
it's not sure the base table has been created with ANSI_NULLS set to ON.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I think the question is not very good, since the correct answer can be deduced without knowledge of indexed views. The successful creation of this unique index would depend on the data in the two columns actually being unique. Since no such assumption is mentioned, it follows that the statement must fail 😉
-
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', 5496reconfigure
-- 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
-
L' Eomot Inversé (10/8/2012)
(i) these are the server defaults for those settingsBut server defaults can be changed. Not that there is much in doing so (read on!), but it can be done. If you write code that should work on servers outside your direct control, it's better not to rely on any defaults at all.
(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
You may use SSMS, but not everyone does.
However, much more important - your statement is incorrect. SSMS does not use the server defaults. If you run a profiler or XEvents trace while working in SSMS, you'll see that each connection emits a series of SET statements when it's opened. These settings are controlled in the Tools / Options / Query Execution / SQL Server / Advanced and ANSI dialogs for new query windows, or in the Query / Query Options / Advanced and ANSI dialogs for already opened query windows. I'm not even sure if all settings are exposed through these dialogs.
If you connect using sqlcmd.exe, you'll see that this tool, too, emits a series of SET options. And they are different from the default options used by SSMS.
So when executing code, the actual options in effect depend upon the tool used (and possibly changes made to that tool's default settings), rather than the server defaults.
(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.
Ah yes. I am waiting for that day as well!
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
kent_secher (10/8/2012)
I think the question is not very good, since the correct answer can be deduced without knowledge of indexed views. The successful creation of this unique index would depend on the data in the two columns actually being unique. Since no such assumption is mentioned, it follows that the statement must fail 😉No, it doesn't have to fail on uniqueness. If it's declared with schemabinding it may fail (it will if there are duplicates in the column set concerned), or it may work (it will if there are no duplicates - for example if the table is empty, which must be a very common case) and when it works will, until it is dropped, enforce uniqueness on these two columns in the table.
Tom
-
Hugo Kornelis (10/8/2012)
However, much more important - your statement is incorrect. SSMS does not use the server defaults. If you run a profiler or XEvents trace while working in SSMS, you'll see that each connection emits a series of SET statements when it's opened. These settings are controlled in the Tools / Options / Query Execution / SQL Server / Advanced and ANSI dialogs for new query windows, or in the Query / Query Options / Advanced and ANSI dialogs for already opened query windows.OK, I should have said that if you haven't donme anything to the server defaults (or have only done what I suggetsed as part of installation) and haven't changed SSMS behaviour from its default using the dialogs you mention, then the settings of the several options needed for indexed views are all present in the server defaults and also in a connection made by SSMS. I over-simplified.
Note Microsoft's clear recommendations:
If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. All DB-Library values must be set correctly either at the server level by using sp_configure or from the application by using the SET command.
and
We strongly recommend that the ARITHABORT user option be set server-wide to ON as soon as the first indexed view or index on a computed column is created in any database on the server.
both on the BoL page referenced by Ron. My recommendation to set it all server-wide on installation is slightly stronger, but I think it's in the same spirit; and I don't think MS would have made the recomendation if they didn't think it would work.
Tom
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply