From a single INSERT statement


  • BINNED

    Continuing the discussion from The Strawman Thread, with a side order of How California’s New Immigration Law Affects Screening Policies:

    @abarker said:

    Or you could share a nice Code SOD ...

    The query itself doesn't look too bad, until you drill down into the view definitions and realise it's all views referencing other views for more levels than I wanted to delve into.

    Exhibit A: the plan diagram from SQL Sentry Plan Explorer. I had to zoom it out quite far to fit it in a single screen

    Exhibit 2: the join diagram (anonymised, the real table and column names are at least relatively sane)

    Exhibit iii: A sample of some of the deeper nested parts of the Plan Tree


  • mod

    You really weren't kidding about a huge nest of SQL …


  • BINNED

    Next proc: first line

        IF OBJECT_ID('tempdb..#tempArrayTable') IS NOT NULL 
            DROP TABLE #tempArrayTable
    

    That inspires confidence that the author knows their SQL.

    (for anyone less familiar with SQL Server [and possibly other systems], #tables are local to the scope of where they're executing. When you've just started a stored proc, there will be no accessible tables in tempdb, and no clashes)


  • mod

    @Jaloopa said:

    Next proc: first line

        IF OBJECT_ID('tempdb..#tempArrayTable') IS NOT NULL 
            DROP TABLE #tempArrayTable
    

    That inspires confidence that the author knows their SQL.

    I'm betting he confused #tempArrayTable and ##tempArrayTable. The two behave enough different that the check is necessary with the double hash, but not the single hash. I actually had to teach my boss (the DBA/CIO) the distinction between the two.



  • @abarker said:

    I'm betting he confused #tempArrayTable and ##tempArrayTable. The two behave enough different that the check is necessary with the double hash, but not the single hash.

    http://i.minus.com/iihDRxhJ7lYXg.gif


  • mod

    Are you saying I'm wrong? Remember we're talking about SQL Server here:

    #temp is the format used to specify a local temporary table. They are private to the process which created them, and are dropped when that process completes. As such, you don't really need to check if the table exists before acting on it. It might be a self defense practice, but it isn't strictly necessary.

    ##temp is the format used to specify a global temporary table. They are private on a per-session basis, and are only dropped when the session is completed. In situations where you have session sharing enabled (such as with many web apps) this means that you cannot accurately predict when a global temporary table will be dropped. In these situations, if you are using global temporary tables, it is best to check if the table exists before performing any operations on it.



  • @abarker said:

    Are you saying I'm wrong?

    Not at all, I don't have in-depth enough knowledge to say that. I just thought it looked like an easy-to-miss gotcha, although your explanation makes it seem less nasty than I first thought.


  • BINNED

    @abarker said:

    global temporary table

    Never heard of these. I can see cases when they might come in handy.

    I think it might be left over debugging code. A lot of procs have the declaration of the variables commented out at the start so you can quickly run the commands when testing changes to the procs. In that situation it makes some sense to drop any temp tables either at the start or the end of the proc


  • mod

    @Jaloopa said:

    Never heard of these. I can see cases when they might come in handy.

    I think it might be left over debugging code. A lot of procs have the declaration of the variables commented out at the start so you can quickly run the commands when testing changes to the procs. In that situation it makes some sense to drop any temp tables either at the start or the end of the proc

    Yeah, global temp tables are nasty things to debug. You definitely want to check if they exist before trying to use them. That construct you shared is actually somewhat common in some of our older procs that mistakenly use global temp tables instead of local temp tables.



  • Not to talk to the rest of the process (nested stack of of views of views of a nested stack of views...)...

    When I was doing reporting from Sql Databases, I would normally include "if #Temp exists drop" logic - yes, # not ## - in my code.

    Why? Temp tables stay alive while logged into SSMS because you are still using the same session:

    • Create table #Temp (....); Insert into #Temp ....; GO
    • Run Query
    • Notice issue
    • Change something in script
    • Rerun Query
    • Query failed, #Temp Table already exists

    Although this would count as "Dev" and the If exists/drop logic could be removed for "Prod", I'd keep it so its there the next time I iterated through it.

    Been awhile since I've been deep into SSMS, but that's my recollection... so THAT part doesn't strike me as WTFery per'se.


  • :belt_onion:

    And I thought Asterisk was bad with _name for "global", __name for "really global", and GLOBAL(name) for "will survive a nuclear war unless you set clearglobalvars=yes".


    Filed under: Ok, I'm exaggerating with _name and __name, at least they don't mix between channels



  • @Jaloopa said:

    Next proc: first line

    IF OBJECT_ID('tempdb..#tempArrayTable') IS NOT NULL 
        DROP TABLE #tempArrayTable
    

    That inspires confidence that the author knows their SQL.

    I've gotten in the habit of putting this in ad-hoc scripts because it's really annoying to have an error halfway down the script that aborts the batch before the DROP TABLE at the end. Sometimes, scripts get integrated into stored procs. It's not really doing any harm, and might be important if the caller expects that table not to exist (more on that below)...

    @Jaloopa said:

    (for anyone less familiar with SQL Server [and possibly other systems], #tables are local to the scope of where they're executing. When you've just started a stored proc, there will be no accessible tables in tempdb, and no clashes)

    Not true. A local temp table can be seen both within the context of its own scope, and any deeper scopes. For example:

    create procedure derp
    as
    begin
      select * from #fleem
    end
    go
    
    create table #fleem
    (
      fleem_id int identity(1,1) primary key,
      fleem_text varchar(50) not null
    )
    
    insert into #fleem
    (fleem_text)
    values
    ('Test'),
    ('Is'),
    ('A'),
    ('This')
    
    exec derp
    
    drop table #fleem
    drop procedure derp
    

    This gets particularly fun when you create temp tables in one stored procedure and then call other stored procedures to do things to them.



  • @abarker said:

    Yeah, global temp tables are nasty things to debug. You definitely want to check if they exist before trying to use them. That construct you shared is actually somewhat common in some of our older procs that mistakenly use global temp tables instead of local temp tables.

    I have yet to see a good use case for them. The "what if two people call this proc at the same time" problem renders them unacceptable for most applications. The only time I've ever seen them in the wild were as the target of bcp loads, and permanent tables in a staging database are probably a better solution for that because they poof more predictably.


  • BINNED

    OK, that's a leaky abstraction then. I verified by creating a stored procedure that creates a temp table, adds data and selects from it, then a script that creates the same table, calls the proc and selects from the outer table

    create procedure test
    as begin
        create table #test (id int)
        insert into  #test values(1)
        select * from #test
    end
    go
    
    create table #test (id int)
        insert into  #test values(2)
        exec test
        select * from #test
    

    output:

    2
    1

  • Discourse touched me in a no-no place

    @Onyx said:

    And I thought Asterisk was bad with _name for "global", __name for "really global", and GLOBAL(name) for "will survive a nuclear war unless you set clearglobalvars=yes".

    That almost sounds like it was written in MUMPS…


  • BINNED

    SELECT DISTINCT
    --some rows
    FROM
    --someview (no joins in the top level query)
    WHERE @someparameter = 1
    OR somecolumn = @someotherparameter
    

    Nice and simple, yeah?

    Query plan:


  • BINNED

    Someview is a select distinct from view2, plus joins
    view2 is a select distinct...group by ( :wtf: ) from view3 (no extra joins)
    view3 is a select from view4 join view5 join view6, plus some tables
    view5 and view6 are two of the other views joined in the definition of someview.

    All I'm trying to do is get to the root where it's actually referencing tables, so I can... um... replace one of the tables with an indexed view. Because we're not meant to modify the tables for the software this software is an addon for, but schemabinding views with indexes are A-OK


  • :belt_onion:

    @Jaloopa said:

    All I'm trying to do is get to the root where it's actually referencing tables, so I can... um... replace one of the tables with an indexed view.

    It's gonna be views all the way down!



  • @Jaloopa said:

    All I'm trying to do is get to the root where it's actually referencing tables, so I can... um... replace one of the tables with an indexed view.

    select name
    from sysobjects
    inner join syscomments on sysobjects.id = syscomments.id
    where xtype='v'
     and text like '%TheNameImLookingFor%'
    

    is probably much better on the eyes in that environment. Note that if the views can be very long you have to concatenate view definitions from syscomments based on the order they appear in, ruled by the "number" column.


  • BINNED

    Ooh, TIL.

    I always assumed definitions would be in some system table or other for sp_helptext to work, but never bothered looking for it



  • @Jaloopa said:

    select distinct

    Yaaarrrrghh....I work with some muppets whose first instinct on seeing "extra" records is to put that in their queries.




  • BINNED

    Just found a page with a query for detangling the dependency tree.

    The most deeply nested view goes to 13 levels of nesting. The query on that page, when run against this view, has 567 rows of dependency references


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.