A Faulty Query & How Does Query Optimizer works?

  • Howdy,

    This is driving me nuts......when I write a query ( In Query Analyser ) to perform a select into ( to load data & create a table at the same time ) all goes well. Then I decide I want to alter the table by adding columns in the same chunk of code. Then I want to select from the newly modified table.

    All goes well if I only do the select into then modify the table, but not if I select into, modify then select again. I have tried using serialised transactions, begin & end statements, stored procedures - no joy.

    I suspect its something to do with the TSQL query optimiser perhaps getting ahead of itself. Is there a certain sequence the query optimiser does things & if so where can I find this info?

    The code :


    select * into #drive from tbl_disk_SQL1

    alter table #drive add [ID] INT IDENTITY (1,1)

    alter table #drive add drive_total INT

    select * from #drive


    Any suggestions welcome!!

    Thanks in advance...


  • Hi there

    Your example works fine for me.

    Can you post your instance version info? have a look through MSDN as well. Ive tried wrappering around different combos of begin end, trans and cant repeat it.



    Chris Kempster


    Author of "SQL Server 2k for the Oracle DBA"

    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Are you actually running the code or just getting a plan?

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

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