I have professed or confessed or prefessed or postfessed previously that I would inscibe the ways I misuse or abuse the trust of the torch beaers of T-SQL best practices by myself practicing the black arts of inefficiency. Even more, I have stated that I would provide the ellusive examples of such self-righteousness. A few comments on the previous post (orbital) post stated to whit “there are times when …things like temp tables…should not be used” (forgive the misquote). So, even while I can not say “never” use:
Select Into #temptable
Exec (any statement to return data)
Select * from #temptable
…I can absolutely say that using such a device is bad…because it carries with it the potential to lock the entire TempDB database, specifically because it can lock several system tables for the duration of the query. (Notice I am not naming specific SQL editions here).
If the query runs for one millisecond, great. If it runs for 30 seconds, not great. Best to avoid it altogether right? The problem is, if you are using a variable to construct the SQL code dynamically (Holy Crap, Batman, he said dynamic SQL – I feel an injection coming on) then it is not possible to use this syntax in a stored proc:
Select @variable = Name from @databasename..sysfiles where groupid = 1
to return the datafile for a database because the variable @databasename is not evaluated in the Select. You need to build the Select and then executed it thusly:
Set @TSQL_L = ‘Select name from ‘+ @dataname + ‘..sysfiles’
Insert Into #temptable
The problem with this is that the Insert Into expects the table to be created already, as opposed to a Select Into. So the point here is to create the table in code, preferably with a drop if exists construct statement as well.
IF EXISTS (SELECT Name FROM tempdb..sysobjects Where name like ‘#temptable’)
–If So Drop it
DROP TABLE #temptable
CREATE TABLE #temptable(
) ON [PRIMARY]
Then execute the code above to insert data into the table.
This may sound like very simple practice and most DBA’s know this. I add it here only to expand the point of when not to use a “bad bad bad practice” code sample, even though it is certainly possible/plausible.
Bad ideas covered: dynamic SQL, temptable with Select Into, Exec() construct:
Good Ideas covered (not really): Test everything in QA/Pre Production. (Seriously?)
Post Mortem / Pre Cursor (not Cursors–next entry)