Post reply

when Changing DB Default SET options.

  • August 31, 2012 at 2:19 am

    #280913

    Hi Friends,

    My problem is: the DML of a table fails, then i found that it is because of a indexed view on it and SET options were not set properly (with the below options)

    ANSI_NULLS

    ANSI_PADDING

    ANSI_WARNINGS*

    ARITHABORT

    CONCAT_NULL_YIELDS_NULL

    NUMERIC_ROUNDABORT

    QUOTED_IDENTIFIER

    My Question: Since, my required SET options here are not the database default, can i change these values at database level (i prefer instead changing in all my SPs)?. what kind of impact analysis should i do. help here please.

    Appreciating your helps.

  • August 31, 2012 at 11:01 am

    #1532536

    is my problem/question not clear guys?

  • August 31, 2012 at 11:23 am

    #1532562

    Sorry to say. It is not clear to me (pretty sure to the others as well), else you could have got the answer.

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 31, 2012 at 11:34 am

    #1532569

    you can change the default settings for your connections in SQL Server Management Studio in the Tools>>Options menu

    as soon as you change it, that would affect all NEW query windows you create from then on...and then each script you run from then on would inherit those settings., whether for create or alter statmeents.

    if you need to toggle back and fouth between options, you are going to want to create a script, or procedure to set them, say based on clicking a keyboard shortcut or two.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 31, 2012 at 12:26 pm

    #1532594

    SQL Mad Rafi (8/31/2012)

    Hi Friends,

    My problem is: the DML of a table fails, then i found that it is because of a indexed view on it and SET options were not set properly (with the below options)

    ANSI_NULLS

    ANSI_PADDING

    ANSI_WARNINGS*

    ARITHABORT

    CONCAT_NULL_YIELDS_NULL

    NUMERIC_ROUNDABORT

    QUOTED_IDENTIFIER

    My Question: Since, my required SET options here are not the database default, can i change these values at database level (i prefer instead changing in all my SPs)?. what kind of impact analysis should i do. help here please.

    Appreciating your helps.

    You can set these at the database level, but you should be aware that your client program is probably setting some or all of them to its defaults at the time a connection is made, so it is not really a good solution.

    This is one reason why I usually avoid an indexed view. If you really need to use an indexed view, you should plan on setting them in your SQL DML code. Also, you may run into issues when you rebuild indexes unless they are set correctly before you do the rebuild.

  • August 31, 2012 at 12:30 pm

    #1532595

    Lowell (8/31/2012)

    you can change the default settings for your connections in SQL Server Management Studio in the Tools>>Options menu

    as soon as you change it, that would affect all NEW query windows you create from then on...and then each script you run from then on would inherit those settings., whether for create or alter statmeents.

    if you need to toggle back and fouth between options, you are going to want to create a script, or procedure to set them, say based on clicking a keyboard shortcut or two.

    Thank you Lowell.

    i knew a bit in how to set the values, but my question was, can i go and change these DEFAULT settings just like that because of my indexed view problem? because i don't want see any other bad impact because of my change.

    Thanks again.

  • August 31, 2012 at 12:35 pm

    #1532601

    Michael Valentine Jones (8/31/2012)

    SQL Mad Rafi (8/31/2012)

    Hi Friends,

    My problem is: the DML of a table fails, then i found that it is because of a indexed view on it and SET options were not set properly (with the below options)

    ANSI_NULLS

    ANSI_PADDING

    ANSI_WARNINGS*

    ARITHABORT

    CONCAT_NULL_YIELDS_NULL

    NUMERIC_ROUNDABORT

    QUOTED_IDENTIFIER

    My Question: Since, my required SET options here are not the database default, can i change these values at database level (i prefer instead changing in all my SPs)?. what kind of impact analysis should i do. help here please.

    Appreciating your helps.

    You can set these at the database level, but you should be aware that your client program is probably setting some or all of them to its defaults at the time a connection is made, so it is not really a good solution.

    This is one reason why I usually avoid an indexed view. If you really need to use an indexed view, you should plan on setting them in your SQL DML code. Also, you may run into issues when you rebuild indexes unless they are set correctly before you do the rebuild.

    Thanks Michael Valentine Jones.

    it is useful.

  • August 31, 2012 at 12:35 pm

    #1532602

    SQL Mad Rafi (8/31/2012)

    Thank you Lowell.

    i knew a bit in how to set the values, but my question was, can i go and change these DEFAULT settings just like that because of my indexed view problem? because i don't want see any other bad impact because of my change.

    Thanks again.

    I'll follow Michael Valentine Jones's Recommendation:

    changing ansi settings should only occur for specific scripts.

    Only change them for specific purposes, in the script at hand, and not in SSMS unless you have a reason.

    ask yourself just how often you really create indexed views,a nd need those ansi settings, vs the times you don't.

    don't change your default settings for a less than 1 percent item you find annoying today, but will not trip over 99% of the time.

    check out this micirosoft post on how Changing ANSI settings can affect your data results:

    http://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply