From a single INSERT statement
-
Continuing the discussion from The Strawman Thread, with a side order of How California’s New Immigration Law Affects Screening Policies:
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
-
You really weren't kidding about a huge nest of SQL …
-
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)
-
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.
-
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.
-
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.
-
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.
-
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
-
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.
-
And I thought Asterisk was bad with
_name
for "global",__name
for "really global", andGLOBAL(name)
for "will survive a nuclear war unless you setclearglobalvars=yes
".
Filed under: Ok, I'm exaggerating with
_name
and__name
, at least they don't mix between channels
-
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)...(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.
-
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.
-
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
-
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…
-
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:
-
Someview
is aselect distinct
fromview2
, plus joins
view2
is aselect distinct...group by
( ) fromview3
(no extra joins)
view3
is a select fromview4 join view5 join view6
, plus some tables
view5
andview6
are two of the other views joined in the definition ofsomeview
.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
-
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!
-
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.
-
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
-
select distinct
Yaaarrrrghh....I work with some muppets whose first instinct on seeing "extra" records is to put that in their queries.
-
-
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