SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View - 8


View - 8

Author
Message
kent_secher
kent_secher
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 183
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 Wink
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14322 Visits: 12197
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

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11020 Visits: 11994
L' Eomot Inversé (10/8/2012)
(i) these are the server defaults for those settings

But 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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14322 Visits: 12197
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 Wink

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

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14322 Visits: 12197
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

bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7835 Visits: 25280
L' Eomot Inversé and Hugo Kornelis

May I thank both of you for the discussion which developed. For myself the main purpose of any one's QOD is to teach those who do not know the correct answer, and for those that do, affirm their knowledge.

In the spirit of community, which binds us together on SSC, you and others like you contribute significantly to the knowledge / understanding of all members.

I believe I speak for a vast majority of our members when I say THANK YOU for your contributions.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Dave62
Dave62
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3245 Visits: 2777
bitbucket-25253 (10/8/2012)
L' Eomot Inversé and Hugo Kornelis

May I thank both of you for the discussion which developed. For myself the main purpose of any one's QOD is to teach those who do not know the correct answer, and for those that do, affirm their knowledge.

In the spirit of community, which binds us together on SSC, you and others like you contribute significantly to the knowledge / understanding of all members.

I believe I speak for a vast majority of our members when I say THANK YOU for your contributions.


Absolutely!!!

I learn much more from reading the discussions than I do from the QotD or by reading BOL. All have their value but I get the most value from the discussions.
tilew-948340
tilew-948340
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 2437
Hugo Kornelis (10/8/2012)

...You may use SSMS, but not everyone does...

Just to let you know that also some people reading the QotD might never use anything else then the SSMS because... well... not every one is a professionnal in SQL server. What seems obvious for most of you can be a revelation for some people. An exemple would be that Analyst-Programmer who has to admin SQL server because his working group is so small and depends on you to learn the basics because the company is so badly structured (and also depends on you to remember all the things you need to remember if you want to be an "OK" SQL server administrator).

Thanks for the question. Still learning...

p.s. by the way, with what do you work if you don't use SSMS?
Error Handler
Error Handler
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 339
Good question +1:-)

Best,
Naseer Ahmad
SQL Server DBA
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11020 Visits: 11994
tilew-948340 (10/8/2012)Just to let you know that also some people reading the QotD might never use anything else then the SSMS because... well... not every one is a professionnal in SQL server.


For the record: I never intended my message to imply that I don't use SSMS, just that some people don't. For most SQL Server professionals, SSMS is the tool of their trade.


p.s. by the way, with what do you work if you don't use SSMS?

I use SSMS for most of my interactive work (querying, checking results, changing query, etc).
But I also save scripts to my filesystem and then execute them using sqlcmd.exe (I have an icon on my desktop that I can just drag&drop a script on - it will automatically fire sqlcmd.exe, execute the script, save the results in a file, then open Notepad when the results). Most people will not create objects in such scripts, but since my main job is to write a T-SQL code generator, my scripts usually contain lots of code to create, drop, or alter database objects.

Other tools that are not used by me (but may be frequently used by others) are SSIS (to create tables to hold imported data), and homebrewn .Net applications.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search