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

  • If select into clause is used into transactions till the transaction commits sysobjects table can not acceses directly.

    It can be accessed only with 'nolock' hint.

    /*

    To verify how blocking occurs I have create these three baches

    1st batch is starting a transaction, creating a table, performing select into operation

    and waiting for 1 minute( this waiting time could be used for other selects or updates)

    2nd batch is the query on sysobjects table

    3rd batch is the query on sysobjects table with (nolock) option

    How to run:

    1. Open three query analyzer windows.

    2. Copy and paste each batch in seperate windows.

    3. fire all the three baches simultanously started with 1st batch.

    Check sp_who for blocked processes

    */

    --------------------------------------------BATCH 1 STARTED--------------------------------------------

    BEGIN TRANSACTION

    IF OBJECT_ID('DIM_CUSTOMER_TMP1') IS NOT NULL DROP TABLE DIM_CUSTOMER_TMP1

    CREATE TABLE DIM_CUSTOMER_TMP1 (TEST VARCHAR(200))

    IF OBJECT_ID('TMP1') IS NOT NULL DROP TABLE TMP1

    SELECT * INTO TMP1 FROM DIM_CUSTOMER_TMP1

    WAITFOR DELAY '000:01:00'

    COMMIT TRANSACTION

    --------------------------------------------BATCH 1 FINISHED--------------------------------------------

    --------------------------------------------BATCH 2 STARTED--------------------------------------------

    SELECT NAME FROM SYSOBJECTS WHERE NAME LIKE '%SYSINDEXES%'

    --------------------------------------------BATCH 2 FINISHED--------------------------------------------

    --------------------------------------------BATCH 3 STARTED--------------------------------------------

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

    --------------------------------------------BATCH 3 FINISHED--------------------------------------------

    Prakash

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

  • You are correct. The problem with SELECT INTO is the way it issues locks to block access to the data. The NOLOCK hint however in a select statement will allow reading of all data including uncomitted data (so you get dirty reads with the hint). This may not be bad for some cases but on a system that relys on committed data only you would not want to do this, especially for calculations. It is better to create your table object first then use INSERT INTO form the table. This will release the locks as soon as the INSERT is done.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • I agree with Antares, create teh table and then insert into it.

    Steve Jones

    steve@dkranch.net

  • And then depending on what you're doing, decide if it shouldnt be a permanent table to start with. You can use the spid to identify records inside a permanent table.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Agree with Antares, Only thing is select into should be fired before you start transaction, If a transaction is started then there is no way to fire a direct query on sysobjects till you commit a transaction.

    Cheers,

    Prakash

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

Viewing 5 posts - 1 through 4 (of 4 total)

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