Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
2
down vote

favorite












I want the default Transaction Isolation level to be SNAPSHOT.



How do I set this on a SQL Server 2016 database?










share|improve this question



















  • 1




    There's not a server or database level setting for this, but sometimes it can be set via the app connection string.
    – sp_BlitzErik
    54 mins ago

















up vote
2
down vote

favorite












I want the default Transaction Isolation level to be SNAPSHOT.



How do I set this on a SQL Server 2016 database?










share|improve this question



















  • 1




    There's not a server or database level setting for this, but sometimes it can be set via the app connection string.
    – sp_BlitzErik
    54 mins ago













up vote
2
down vote

favorite









up vote
2
down vote

favorite











I want the default Transaction Isolation level to be SNAPSHOT.



How do I set this on a SQL Server 2016 database?










share|improve this question















I want the default Transaction Isolation level to be SNAPSHOT.



How do I set this on a SQL Server 2016 database?







sql-server sql-server-2016 snapshot-isolation






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 52 mins ago









Paul White♦

47.9k14257407




47.9k14257407










asked 1 hour ago









Inquisitor Shm

1232




1232







  • 1




    There's not a server or database level setting for this, but sometimes it can be set via the app connection string.
    – sp_BlitzErik
    54 mins ago













  • 1




    There's not a server or database level setting for this, but sometimes it can be set via the app connection string.
    – sp_BlitzErik
    54 mins ago








1




1




There's not a server or database level setting for this, but sometimes it can be set via the app connection string.
– sp_BlitzErik
54 mins ago





There's not a server or database level setting for this, but sometimes it can be set via the app connection string.
– sp_BlitzErik
54 mins ago











1 Answer
1






active

oldest

votes

















up vote
5
down vote













It is not possible to configure Snapshot Isolation (SI) as the default isolation level.



To use SI, the database must be enabled for Snapshot Isolation:



ALTER DATABASE CURRENT
SET ALLOW_SNAPSHOT_ISOLATION ON;


Then each connection must explicitly request SI, using for example:



SET TRANSACTION ISOLATION LEVEL SNAPSHOT;


It is possible to set the default Read Committed isolation level to use row versioning (RCSI).



If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:



ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON;


Further reading:




  • Snapshot Isolation in SQL Server (documentation)


  • How to set the default transaction isolation level server wide? by Nacho Alonso Portillo





share|improve this answer






















    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221548%2fis-it-possible-to-set-default-transaction-isolation-for-all-connections-in-sql-s%23new-answer', 'question_page');

    );

    Post as a guest






























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    5
    down vote













    It is not possible to configure Snapshot Isolation (SI) as the default isolation level.



    To use SI, the database must be enabled for Snapshot Isolation:



    ALTER DATABASE CURRENT
    SET ALLOW_SNAPSHOT_ISOLATION ON;


    Then each connection must explicitly request SI, using for example:



    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;


    It is possible to set the default Read Committed isolation level to use row versioning (RCSI).



    If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:



    ALTER DATABASE CURRENT
    SET READ_COMMITTED_SNAPSHOT ON;


    Further reading:




    • Snapshot Isolation in SQL Server (documentation)


    • How to set the default transaction isolation level server wide? by Nacho Alonso Portillo





    share|improve this answer


























      up vote
      5
      down vote













      It is not possible to configure Snapshot Isolation (SI) as the default isolation level.



      To use SI, the database must be enabled for Snapshot Isolation:



      ALTER DATABASE CURRENT
      SET ALLOW_SNAPSHOT_ISOLATION ON;


      Then each connection must explicitly request SI, using for example:



      SET TRANSACTION ISOLATION LEVEL SNAPSHOT;


      It is possible to set the default Read Committed isolation level to use row versioning (RCSI).



      If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:



      ALTER DATABASE CURRENT
      SET READ_COMMITTED_SNAPSHOT ON;


      Further reading:




      • Snapshot Isolation in SQL Server (documentation)


      • How to set the default transaction isolation level server wide? by Nacho Alonso Portillo





      share|improve this answer
























        up vote
        5
        down vote










        up vote
        5
        down vote









        It is not possible to configure Snapshot Isolation (SI) as the default isolation level.



        To use SI, the database must be enabled for Snapshot Isolation:



        ALTER DATABASE CURRENT
        SET ALLOW_SNAPSHOT_ISOLATION ON;


        Then each connection must explicitly request SI, using for example:



        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;


        It is possible to set the default Read Committed isolation level to use row versioning (RCSI).



        If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:



        ALTER DATABASE CURRENT
        SET READ_COMMITTED_SNAPSHOT ON;


        Further reading:




        • Snapshot Isolation in SQL Server (documentation)


        • How to set the default transaction isolation level server wide? by Nacho Alonso Portillo





        share|improve this answer














        It is not possible to configure Snapshot Isolation (SI) as the default isolation level.



        To use SI, the database must be enabled for Snapshot Isolation:



        ALTER DATABASE CURRENT
        SET ALLOW_SNAPSHOT_ISOLATION ON;


        Then each connection must explicitly request SI, using for example:



        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;


        It is possible to set the default Read Committed isolation level to use row versioning (RCSI).



        If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:



        ALTER DATABASE CURRENT
        SET READ_COMMITTED_SNAPSHOT ON;


        Further reading:




        • Snapshot Isolation in SQL Server (documentation)


        • How to set the default transaction isolation level server wide? by Nacho Alonso Portillo






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited 41 mins ago

























        answered 52 mins ago









        Paul White♦

        47.9k14257407




        47.9k14257407



























             

            draft saved


            draft discarded















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221548%2fis-it-possible-to-set-default-transaction-isolation-for-all-connections-in-sql-s%23new-answer', 'question_page');

            );

            Post as a guest













































































            Comments

            Popular posts from this blog

            What does second last employer means? [closed]

            Installing NextGIS Connect into QGIS 3?

            One-line joke