Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

View - 8 Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 4:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 6:21 AM
Points: 276, Visits: 180
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 ;)
Post #1369698
Posted Monday, October 8, 2012 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 7,742, Visits: 9,490
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
Post #1369724
Posted Monday, October 8, 2012 5:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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
Post #1369731
Posted Monday, October 8, 2012 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 7,742, Visits: 9,490
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
Post #1369738
Posted Monday, October 8, 2012 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 7,742, Visits: 9,490
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
Post #1369750
Posted Monday, October 8, 2012 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:17 PM
Points: 5,332, Visits: 25,261
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
Post #1369783
Posted Monday, October 8, 2012 7:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 2,085, Visits: 2,082
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.
Post #1369805
Posted Monday, October 8, 2012 7:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
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?
Post #1369809
Posted Monday, October 8, 2012 7:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:36 AM
Points: 679, Visits: 292
Good question +1

Best,
Naseer Ahmad
SQL Server DBA
Post #1369816
Posted Monday, October 8, 2012 8:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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
Post #1369832
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse