temporary tables or table-type variables BAD???

Hi experts

i am reading about tuning

i do not understand this
Minimize the use of temporary tables or table-type variables. Use sub-queries, table expressions or window functions

can yo give me an exmple in TSQL code
enrique_aeoAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
1) Yes
2) Would be tempting to say In memory but is also manifested in tempdb
3) when you need data to exist outside the scope of a temp table (session) - there are global temp tables
4) too hard to answer succinctly - I use them all the time....
5) table variables must be declared so are only available within the scope of a declared variable (ie the batch)
6) I cannot say - I dont use them all that much - within a batch needing a table like structure with small datasets

One difference that I do take advantage of is the User Defined Function where we can use Table Variables, but cannot create a temp table

I think Brent Ozar sums it up well for table variables : https://www.brentozar.com/blitzcache/table-variables/
And more reading for you : https://www.sqlshack.com/when-to-use-temporary-tables-vs-table-variables/
0
 
arnoldCommented:
Instead of select into #table or ##table or @table

; with cte
Columns
As (select data from
Could include joins

)

Select from sometable join cte on ...


Pivot examples provide example on common table expression

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
Select where column in (select refcolumn from pother table where criteria)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Where are you using this?
I don't really totally agree with that statement.
0
 
Mark WillsTopic AdvisorCommented:
Temp tabless and table variables arent bad per se, but, they shouldnt really be considered "the first step".

Need to really consider what you need to achieve and then decide the better approach.

Often enough, a subquery or a Common Table Expression  (CTE) can achieve a LOT. They are similar in nature and essentially use a sub-query approach.

CTE you basically name your subquery and do things with it. A subquery yor basically select from it.

CTE:
With CTE_Name as
(
   select things like any select statement
)
   select * from CTE_name

Open in new window

Subquery:
Select * from
   (select things like any select statement) as data

Open in new window

Many will promote one over the other, but pretty much same thing. The equivalent of  temp table or table variable would be
create table #mytable (col1 int, col2 ... etc ...)
insert #mytable (col1 etc)
select things like any select statement

-- then select from #mytable

-- the other way temp tables are often used is 

select things like any select statement INTO #my-uniquely-named-table

Open in new window


There are times when any one of the approaches might prove advantageous. The challenge with T-SQL is deciding which approach is best for a given problem.
0
 
enrique_aeoAuthor Commented:
hi experts

1. temporary tables are created in the tempdb, is it true?
2. where are table-type variables created?
3. in which scenarios I would not use a temporary table
3. in which scenarios I would use a temporary table
5. in which scenarios I would not use a table-type variable
6. in which scenarios I would use a table-type variable
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.