SQL Server Performance

@@TRANCOUNT difference between SQL 7 and SQL 2000

Discussion in 'General Developer Questions' started by neil_rutherford, Aug 7, 2004.

  1. neil_rutherford New Member

    During testing of an application, i noticed a difference between
    SQL 2000 and SQL 7, both with identical config.

    In a nutshell:
    A table has a trigger for UPDATE and DELETE.
    When a column in the table is UPDATED the following happens:

    In autocommit mode, when entering a trigger the trancount equals
    1 for both SQL 7 and 2000.

    When the same update is performed in an explicit transaction
    in SQL 7 @@TRANCOUNT equal 2, and in SQL 2000 @@TRANCOUNT equals 1.

    Configuration is the same and there are no implicit transactions.

    I don't need a work around as this will invalidate the migration
    process as both products should behave identically.
    What would influence the difference or why is there a difference???
    Is there something which has been overlooked?


    The following code replicates the problem

    Ensure implicit transactions are off in both versions at the server
    level, thus defaulting to autocommitted mode.
    Ensure sp_configure settings are identical.

    Step 1: Create a DB called test:

    Step 2: Execute the following under the context of test DB.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)

    drop trigger [dbo].[trigtest]

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[test]

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[trancount]

    CREATE TABLE [dbo].[test] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [text] [char] (10) NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[trancount] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [trancount] [int] NOT NULL
    ) ON [PRIMARY]

    CREATE TRIGGER trigtest ON [dbo].[test]
    declare @trancount int

    select @trancount = @@TRANCOUNT

    insert into trancount ( trancount ) values ( @trancount )

    Step 3: Run the following against the DB, then check trancount table.

    -- Add a record to the test table (trigger will not fire)
    insert into test (text) values ( 'xxxx' );

    -- Update the value (autocommit mode) to fire trigger
    -- Under SQL 7 and 2000, trancount table will only indicate 1 tranaction open.
    -- This is being performed in autocommit mode.
    update test set text = 'test1'

    -- Update value using an explicit transaction
    -- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000 trancount equals 1
    begin transaction
    update test set text = 'test2'
    commit work

Share This Page