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

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  October 12, 2007 at 10:48 am

  #375633

  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

• Hans Lindgren

  SSChampion

  Points: 10496

  More actions

  February 11, 2008 at 3:30 am

  #776829

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

  Regards,

  Hans Lindgren

• simonxy

  Grasshopper

  Points: 21

  More actions

  February 14, 2012 at 6:02 am

  #1446795

  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