Temporary objects

Question

Temporary objects

Ron McCullough

SSC Guru

Points: 63877
More actions
December 30, 2009 at 8:09 pm

#215358

Comments posted to this topic are about the item Temporary objects
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

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

ziangij SSCrazy Eights Points: 8066 More actions · Answer 1

Hi,

Is there any real use of such a temporary stored procedure ? can somebody please provide an example of where this can be used?

Thanks.

Mighty SSCrazy Eights Points: 9063 More actions · Answer 2

Hi,

I don't agree with the statement in the explanation that this is an "undocumented ability".

Both "global temporary procedures" and "global temporary tables" can be found in the index of BOL, and they bring you to the normar "CREATE TABLE" and "CREATE PROCEDURE" T-SQL topics in BOL.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

THANKS FOR THE QOD BitBucket

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

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

An interesting QOD, educating people about some little-known features. But:

1) Why 3 points? It's not a hard question at all; everyone who knows about global temp objects (which SHOULD be at least half the regular users here) should get it right at first glance, and guessers have a good chance as well since there are only 2 credible options (and it's easy to just try it out)

2) Why the need for seperate instances? The exact same effect can be had with less effort by using two connections from one instance of SSMS / QA.

But most important - PLEASE do a bit of research before submitting a QotD. Both local and global temporary objects arre fully documented in Books Online. Just use the index to look under any of the keywords "CREATE TABLE", "CREATE PROCEDURE", or "Temporary Tables" to find it. I am also quite surprised that Steve didn't catch this obvious oversight.

Oh well. Blame it on the bubbly drinks.

A very good 2010 to everyone!

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/

setiv Mr or Mrs. 500 Points: 556 More actions · Answer 5

Hi.

Happy New Year 2010 to all.

Where I can search documents about undocumented ability ???

And I want to say about http://www.microsoft.com/Sqlserver/2005/en/us/express-down.aspx#SP3

that set1500<>set1700 and set1500<>set2010 and set1700<>set2010.

And immediately I remember Prince Igor by Borodin,

Silva by Kalman and Jesus Christ by The Bittles.

There is the present Versailles.

I sincerely want to believe that this scheme is suitable

for the most important professionals as a demo version.

Sergey

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

setiv (1/2/2010)
Where I can search documents about undocumented ability ???

If a feature is undocumented, then you generally will not find any documents about it easily - that's exactly what makes the feature undocumented. Sometimes, google will turn up something. But keep in mind that undocumented features are often undocumented for a reason - they may be an unintended side effect of some implementation choices that could nchange in a future version. Undocumented features should never be relied upon.

But the features that were described in this question are not undocumented at all. They are widely documented in Boos Online. See my previous reply in this thread for some places where you could find this documentation.

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/

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 7

@setiv,

I agree with what Hugo said. Just wanted to add one small info which can partially help your cause.

sp_helpextendedproc is one sp which lists all the extended sps ( including the undocumneted one's)

along with the DLLs used. As quite a few of the undocumented one's are extended sp's this one can be useful.

You can also go thro this link

http://strictlysql.blogspot.com/2009/09/undocumented-sps-part-ii.html

Regards,
Raj

http://Strictlysql.blogspot.com

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 8

I am sorry, but I am this poor little new one that is trying to understand SQLserver and, to this question, I do not understand the answer. The question is "What IS the returned value of rows inserted? " and if you execute the commands, all it returns is 3 rows saying "(1 row(s) affected)", so THE return value is 3 sentences but there is not a "ONE returned value"... So how come the answer could be "3"???

Steve Jones - SSC Editor SSC Guru Points: 736211 More actions · Answer 9

My apologies. It made sense to me, but really what is being asked is how many rows are inserted.

I have edited the question.

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

tilew-948340 (1/2/2010)
I am sorry, but I am this poor little new one that is trying to understand SQLserver and, to this question, I do not understand the answer. The question is "What IS the returned value of rows inserted? " and if you execute the commands, all it returns is 3 rows saying "(1 row(s) affected)", so THE return value is 3 sentences but there is not a "ONE returned value"... So how come the answer could be "3"???

The answer is very clear. (At least, it WAS very clear, until Steve edited it).

Before the code block, there are instructions on what to run in what order. After points 1, 2, and 3, there is a 4th (unnumbered - okay, THAT might be somewhat confusing) point:

"SELECT COUNT(*) AS 'Rows Inserted' FROM ##Test. - This statement may be executed in either instance of SSMS or QA"

And then the question was what this query returns.

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/

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

Ugh, sorry, I was looking at the code block and didn't see the query above. I've put it back the way it was to keep Hugo happy 🙂

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

Wow, I never knew I had that kind of influence on you, Steve.

Too bad I'm not allowed to run for president, as I'd be sure of at least one vote 😀

Thanks for changing it back;-)

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/

setiv Mr or Mrs. 500 Points: 556 More actions · Answer 13

You probably encountered situations where the query returns

the correct result when one is the configuration database,

but with a different configuration gets completely incorrect results.

If you use the undocumented methods, you can be charged

damage at 1000000000$ or 1,000,000 lives.

Therefore is this question.

setiv Mr or Mrs. 500 Points: 556 More actions · Answer 14

You probably encountered situations where the query returns

the correct result when one is the configuration database,

but with a different configuration gets completely incorrect results.

If you use the undocumented methods, you can be charged

damage at 1000000000$ or 1,000,000 lives.

Therefore is this question.