Temporary objects

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

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

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

  • 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

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

  • 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

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

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

  • 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"???

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

    I have edited the question.

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

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

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

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

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

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

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