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 ««123»»

simple que Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 10:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
harri.reddy (9/14/2012)
thanks for your response,

for my other question,

it was something ,if i define global variable in sp,will it can be access in inside stored procedure


Could you please advise how did you managed to define a global variable in sp?
Is it some kind of hidden SQL Server feature?
Thanks.


_____________________________________________
"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 #1359471
Posted Friday, September 14, 2012 10:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 41,532, Visits: 34,451
harri.reddy (9/14/2012)
it was something ,if i define global variable in sp,will it can be access in inside stored procedure


As I mentioned above, SQL Server doesn't have global variables.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1359493
Posted Friday, September 14, 2012 10:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 41,532, Visits: 34,451
bala.a (9/14/2012)
Literally speaking,

a) "Select *" will return the result set , whereas "Count(*)/Count(1)" will return a scalar value and this will result in significance difference in terms of I/O


IO, maybe, maybe not. Depends on whether there are indexes available.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1359494
Posted Friday, September 14, 2012 10:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
Eugene Elutin (9/14/2012)
harri.reddy (9/14/2012)
thanks for your response,

for my other question,

it was something ,if i define global variable in sp,will it can be access in inside stored procedure


Could you please advise how did you managed to define a global variable in sp?
Is it some kind of hidden SQL Server feature?
Thanks.


Yes, is a hidden, undocumented feature that can be only used by a few members of a secret society. Gail might know but she won't tell us as her rights would be dismissed.
To note: that was a joke.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1359503
Posted Friday, September 14, 2012 5:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
do you mean global ##temptables

These persist between stored procedures until the session is ended or the connection is dropped.

you can fill a ##temptable in one procedure and access the data in another, but it is generally not good practice.
Post #1359744
Posted Monday, September 17, 2012 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
aaron.reese (9/14/2012)
do you mean global ##temptables

These persist between stored procedures until the session is ended or the connection is dropped.

you can fill a ##temptable in one procedure and access the data in another, but it is generally not good practice.


That is not exactly correct...
First of all, it can be done even with local #temptable. If you create this table in one proc you can use it in a subsequent "chained" proc. Try this:

create proc p1
as
begin
create table #tP (a int)
exec p2
select * from #tP
end
go

create proc p2
as
begin
insert #tP select 11111
end
go

exec p1
go


The second thing about global ##temptables is they are not only accessible by the "chained" proc executed within the one which creates a global temp table, but also it can be accessed by any query in other connection until the connection, which used to create this global temptable is closed or any other process dropped it.




_____________________________________________
"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 #1360055
Posted Monday, September 17, 2012 4:49 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: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
I thought the ## temp tables persisted until they were explicitly dropped from the TempDb, or the SQL service was restarted.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1360094
Posted Monday, September 17, 2012 5:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Jason-299789 (9/17/2012)
I thought the ## temp tables persisted until they were explicitly dropped from the TempDb, or the SQL service was restarted.


Just try:
1. Open query window 1 and type: create table ##t (a int)
2. Open query window 2 , type and execute: select * from ##t
3. Close query window 1.
4. Try to execute the query in window 2

According to BoL:

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.


Now, there is a small details, even so the query window 2 still has a SELECT query, it doesn't really reference the table at the moment when you closed the windows 1, therefore ##t is dropped.

However all will change if you will start again and instead of simple SELECT in as second query window execute:

begin tran
insert ##t select 1
select * from ##t
...

Now that is really cool one. You may try yourself:

1. Open query window 1 and type: create table ##t (a int)
2. Open query window 2, type and execute:

begin tran
insert ##t select 1

3. Open query window 3, type and execute: select * from ##t. - it will not return as transaction in #2 is still open

4. Close window 1. Query in #3 is still waiting for transaction in #2 ...

5. Commit or roll back transaction in #2. Query in #3 will return (something or nothing depending of what you have done- commit or rollback

6. Try to execute query again in window #3









_____________________________________________
"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 #1360115
Posted Monday, September 17, 2012 6:02 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: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
Thanks Eugine, as I said I thought they were persisted even after the process that created them was closed,

I was actually confused with creating a specific fixed table,Eg Create Table Tempdb..Test (Col1 int) within the temp db.

so added that to my knowledge base, along with following the Natural Key vs IDENTITY thread.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1360134
Posted Monday, September 17, 2012 6:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Jason-299789 (9/17/2012)
Thanks Eugine, as I said I thought they were persisted even after the process that created them was closed,

I was actually confused with creating a specific fixed table,Eg Create Table Tempdb..Test (Col1 int) within the temp db.

so added that to my knowledge base, along with following the Natural Key vs IDENTITY thread.


Yeah, I think that this discussion (about Global ##Temp tables) is quite important. My experience tells me that it's quite common for SQL developers starting using global temp table without complete understanding of global temp tables usage aspects eg. their life-span and its pre-conditions. Hopefully, the shown example will give more thoughts into it.
Regarding of the Natural Key vs IDENTITY thread, I feel a bit guilty there... We I have hijacked someone else thread - not very good, isn't it!


_____________________________________________
"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 #1360149
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse