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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.