Temp table limitations

Question

Temp table limitations

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Steve Jones - SSC Editor (5/24/2014)
Hany Helmy (5/24/2014)
SQLRNNR (5/23/2014)
Steve Jones - SSC Editor (5/23/2014)
Question changed and points awarded back as you can apparently partition a temp table: http://www.sqlservercentral.com/blogs/sqlrnnr/2014/05/23/can-you-partition-a-temporary-table/
Thanks Steve.
And by the way - thanks for continuing this feature and providing questions for us.
And thank u Jason for correcting the question, also for the useful link, although I never experienced (or even heard about) a production case for partitioning temp tables.
Not sure there is one. I suspect this is a bug, and that the design was that this wouldn't be allowed.

That's probably true. When thinking about it, you would have to recreate the partition function and scheme after each service restart or at least so it seems.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Uwe Ricken Hall of Fame Points: 3169 More actions · Answer 2

Hi Steve,

curious. I've selected "no FK" as one and only option but it gave me "failed" 🙂

However - local temptable are valid in the sessein and they can have indexes and constraints as well.

What was my "mistake" when I have selected "no fk" as single option?

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Uwe Ricken (5/25/2014)
Hi Steve,
curious. I've selected "no FK" as one and only option but it gave me "failed" 🙂
However - local temptable are valid in the sessein and they can have indexes and constraints as well.
What was my "mistake" when I have selected "no fk" as single option?

Hi Uwe,

Your mistake was twofold:

1. You overlooked the "select 2" in the question

2. You didn't realize that Steve wrote "local" but actually meant to wrote "global".

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Uwe Ricken Hall of Fame Points: 3169 More actions · Answer 4

Hallo Hugo,

aaargh - thank you for pointing to that failure.

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

I got it wrong, but the "correct" answers stumped me. How are "local" temporary tables visible across sessions?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Nakul Vachhrajani (5/25/2014)
I got it wrong, but the "correct" answers stumped me. How are "local" temporary tables visible across sessions?

They are not. Take a look at the notes posted by Hugo.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQLRNNR (5/24/2014)
When thinking about it, you would have to recreate the partition function and scheme after each service restart or at least so it seems.

Or create them in Model.

Can't think of a valid reason to do this in production either.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave62 SSCertifiable Points: 7459 More actions · Answer 8

I don't think the mistake in the corrected answers is that Local should be Global because we are looking for limitations as the correct answers. Probably the word not should have been used in the Local answer.

"Local temp tables are not visible to all sessions"

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 9

PChiragS

SSCarpal Tunnel

Points: 4965

May 27, 2014 at 8:14 am

#1716703

thanks steve for the question.

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

Hugo Kornelis (5/23/2014)
I was surprised to see so many people claim that this is an easy question, since there is a very glaring error - there is only one error.
But after reading the discussion I understand that the question was changed. Not for the better, though.
Local temporary tables are not visible to all sessions, only to the current session. Global temporary tables are visible to all sessions.

I'm glad I'm not the only one who found the question was difficult since I could only find 1 correct answer and there was supposed to be 2.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 11

Hugo Kornelis (5/25/2014)
Uwe Ricken (5/25/2014)
Hi Steve,
curious. I've selected "no FK" as one and only option but it gave me "failed" 🙂
However - local temptable are valid in the sessein and they can have indexes and constraints as well.
What was my "mistake" when I have selected "no fk" as single option?
Hi Uwe,
Your mistake was twofold:
1. You overlooked the "select 2" in the question
2. You didn't realize that Steve wrote "local" but actually meant to wrote "global".

good catch. I was thinking which things weren't valid instead of the other way around.