From a single INSERT statement

Jaloopa

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

abarker

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

Jaloopa

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)

abarker

@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.

hungrier

@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

abarker

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.

Phil Factor / Aug 1, 2020

Temporary Tables in SQL Server - Simple Talk

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process...

Archiveddocs

Special Table Types

hungrier

@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.

Jaloopa

@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

abarker

@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.

WernerCD

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.

Onyx

http://i0.kym-cdn.com/entries/icons/original/000/005/954/wtf_is_this_shit2_RE_73_Million_Sharks_Killed_Every_Year-s468x349-71815.jpg

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

Groaner

@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.

Groaner

@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.

Jaloopa

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

dkf

@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…

Jaloopa

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:

Jaloopa

Someview is a select distinct from view2, plus joins
view2 is a select distinct...group by ( ) 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

Onyx

@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!

dsckeld

@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.

Jaloopa

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

boomzilla

@Jaloopa said:

select distinct

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

VaelynPhi

Nuke the entire site fom orbit – 00:09
— Nameless Voice

Jaloopa

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

Jen McCown / Jun 30, 2010

Detangling Nested Views < Jen McCown

I hate nested views. I hate them, I hate them, I hate them. This is the Nth shop I have been in that makes use of one view that pulls FROM and JOINs other views, which in turn pull from other views, and it’s foot-stompingly frustrating to work with. I’ve talked before about how inappropriate […]

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