Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL - tempdb


TSQL - tempdb

Author
Message
Sankar Reddy
Sankar Reddy
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 1250
Comments posted to this topic are about the item TSQL - tempdb

Sankar Reddy | http://SankarReddy.com/
TraderSam
TraderSam
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 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!Wink
Source-NH
Source-NH
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1651 Visits: 479
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.
Longy
Longy
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 944
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



OCTom
OCTom
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2859 Visits: 4152
This doesn't work on 2000 but it does on 2005.

Baseball is back! w00t
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 Visits: 2332
How many rows would this return? Smile (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

Source-NH
Source-NH
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1651 Visits: 479
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.
Longy
Longy
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 944
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!



TraderSam
TraderSam
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 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!Wink
WayneS
WayneS
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 10435
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
Author - SQL Server T-SQL Recipes
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search