Never use select into in transactions......

  • Comments posted to this topic are about the item Never use select into in transactions......

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • This is no longer true in SQL Server 2005...

    Regards,

    Hans Lindgren

  • I have sql2008 r2 with READ_COMMITTED_SNAPSHOT ON.

    So, default database isolation level is read commited with row versioning.

    If i try this:

    --first connection

    BEGIN TRAN

    SELECT 1 AS test INTO dbo.test

    --second connection

    SELECT * FROM SYSOBJECTS

    OR

    SELECT * FROM SYSOBJECTS WITH (NOLOCK) WHERE NAME LIKE '%test%'

    This wont work.

    So, the sys.objects is not available even if I have snapshot isolation with row versioning.

    Is there any solution? I would like to read from sys.objects and test table should not be visible there until commit of first connection.

    lp,S

Viewing 3 posts - 1 through 2 (of 2 total)

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