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

TSQL - tempdb Expand / Collapse
Author
Message
Posted Thursday, February 26, 2009 11:40 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 9:58 AM
Points: 382, Visits: 1,177
Comments posted to this topic are about the item TSQL - tempdb

Sankar Reddy | http://SankarReddy.com/
Post #665524
Posted Friday, February 27, 2009 5:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, Visits: 565
Why would I get zero and zero when I run this set of queries on SQL 2005?

If it was easy, everybody would be doing it!;)
Post #665633
Posted Friday, February 27, 2009 6:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
Based on the queries supplied, you should get 1 and 1, because the first statement includes the wildcard at the beginning, so the # SHOULD be returned.
Post #665650
Posted Friday, February 27, 2009 6:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:43 AM
Points: 1,480, Visits: 931
Rich (2/27/2009)
Based on the queries supplied, you should get 1 and 1, because the first statement includes the wildcard at the beginning, so the # SHOULD be returned.


The answer says

"Table variable names inside tempdb starts with '#' followed by hex string"

In which case it wouldn't find it using the name



Post #665659
Posted Friday, February 27, 2009 6:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:14 AM
Points: 2,605, Visits: 3,956
This doesn't work on 2000 but it does on 2005.

Baseball is back!
Post #665675
Posted Friday, February 27, 2009 6:47 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:05 AM
Points: 644, Visits: 2,141
How many rows would this return? :) (yes...it's different)

use AdventureWorks
go
declare @tableA TABLE(col1 int, col2 int)
go
--stmt 1
select name from tempdb.sys.tables where name like '%tableA%'
--stmt 2
select name from tempdb.sys.columns where name like 'col1%'



Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #665677
Posted Friday, February 27, 2009 6:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
Longy (2/27/2009)
Rich (2/27/2009)
Based on the queries supplied, you should get 1 and 1, because the first statement includes the wildcard at the beginning, so the # SHOULD be returned.


The answer says

"Table variable names inside tempdb starts with '#' followed by hex string"

In which case it wouldn't find it using the name


Oops. Too early in the AM.
Post #665684
Posted Friday, February 27, 2009 6:55 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:43 AM
Points: 1,480, Visits: 931
Rich (2/27/2009)
Longy (2/27/2009)
Rich (2/27/2009)
Based on the queries supplied, you should get 1 and 1, because the first statement includes the wildcard at the beginning, so the # SHOULD be returned.


The answer says

"Table variable names inside tempdb starts with '#' followed by hex string"

In which case it wouldn't find it using the name


Oops. Too early in the AM.


Yeah, I thought exactly the same thing at first, but after re-reading it spotted that!



Post #665688
Posted Friday, February 27, 2009 6:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, Visits: 565
Thanks, skjoldtc!

When you said that I checked my compatibility level on the database I ran the queries on and they were set to 80 which is why I got zeros when I ran the queries.




If it was easy, everybody would be doing it!;)
Post #665692
Posted Friday, February 27, 2009 8:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 5,364, Visits: 8,954
Interesting.

I modified the supplied code to get the # of tempdb objects, and all tempdb table names:
use AdventureWorks
go
select count(*) from tempdb.sys.objects
select name from tempdb.sys.tables --where name like '%tableA%'
declare @tableA TABLE(col1 int, col2 int)
--stmt 1
select name from tempdb.sys.tables --where name like '%tableA%'
--stmt 2
select name from tempdb.sys.columns where name like 'col1%'
select count(*) from tempdb.sys.objects

and I find that there are no new tables or objects between the two selects from sys.tables.

So I modified the sys.columns select to:
select sc.name [ColName], st.name [TableName] 
from tempdb.sys.columns sc
INNER JOIN tempdb.sys.tables st ON st.object_id = sc.object_id
where sc.name like 'col1%'

What I'm seeing is that the tablename for this table is already in use before the table is declared. I've even tried stopping and restarting the sql service for this instance.

What's up with this?


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #665763
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse