Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Global Variable Expand / Collapse
Author
Message
Posted Wednesday, July 31, 2013 1:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 5,255, Visits: 12,180
If you have a couple of these suckers, and they are decently complex, the temp table is not a bad idea.


On this, I think we shall have to agree to disagree.

From BOL:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


If this stuff is worth storing in a table and is going to be referenced by multiple scripts or procs, it should be held in a permanent table, not one which may disappear unexpectedly.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1479322
Posted Wednesday, July 31, 2013 1:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 816, Visits: 742
Phil Parkin (7/31/2013)
[quote]If this stuff is worth storing in a table and is going to be referenced by multiple scripts or procs, it should be held in a permanent table, not one which may disappear unexpectedly.


My assumption was that we were talking about a single script, consisting of multiple batches. In which case a local temp table would do.

If we are talking about a multitude of scripts, global variables would not work, even if they existed(*). In that case, maybe the best option would be user-defined functions.

(*) Those who have worked with SQL Server for a long time, know that once there was something called "global variables", and indeed they had leading @@. But they were all pre-defined and readonly, that is @@spid, @procid and the like. Today they are referred to as functions, but you can still use them in context where normal built-in functions cannot be used.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1479326
Posted Wednesday, July 31, 2013 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 5,255, Visits: 12,180
My assumption was that we were talking about a single script, consisting of multiple batches. In which case a local temp table would do


I see! I agree with your comments in that case



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1479337
Posted Wednesday, July 31, 2013 3:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189

(*) Those who have worked with SQL Server for a long time, know that once there was something called "global variables", and indeed they had leading @@. But they were all pre-defined and readonly, that is @@spid, @procid and the like. Today they are referred to as functions, but you can still use them in context where normal built-in functions cannot be used.


Do you mean Sybase? I have worked with SQL server since their rebranded version of Sybase. Some people might called them as they used to in Sybase, but I cannot remember that these ones were ever refered to as "global variables" in MS SQL specs. Well, I could be very wrong, as it was really long-long ago, when I couldn't read in English .


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1479361
Posted Wednesday, July 31, 2013 12:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 816, Visits: 742
Eugene Elutin (7/31/2013)

Do you mean Sybase? I have worked with SQL server since their rebranded version of Sybase. Some people might called them as they used to in Sybase, but I cannot remember that these ones were ever refered to as "global variables" in MS SQL specs.


Here is a screenshot from Books Online for SQL 6.5 to prove it.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se

  Post Attachments 
BOL65.jpg (6 views, 86.88 KB)
Post #1479649
Posted Thursday, August 1, 2013 3:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
Erland Sommarskog (7/31/2013)
Eugene Elutin (7/31/2013)

Do you mean Sybase? I have worked with SQL server since their rebranded version of Sybase. Some people might called them as they used to in Sybase, but I cannot remember that these ones were ever refered to as "global variables" in MS SQL specs.


Here is a screenshot from Books Online for SQL 6.5 to prove it.


No need in proofs. I believe you! MS took Sybase docs and just searched-and-replaced "Sybase" with "SQL Server", why wouldn't they - it did saved them money and time...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1479842
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse