SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL - tempdb


TSQL - tempdb

Author
Message
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4217 Visits: 18732
Gaby Abed (2/27/2009)
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%'



I must admit - you TOTALLY had me... I was sitting here going "what??!?!", nothing is different! I even pasted the selects next to each other to make sure they were the same and hadn't changed. So why the different results?

I guess you can tell me "where to go". BigGrin
GOT ME!

Chad
GabyYYZ
GabyYYZ
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1753 Visits: 2336
Chad Crawford (2/27/2009)
Gaby Abed (2/27/2009)
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%'



I must admit - you TOTALLY had me... I was sitting here going "what??!?!", nothing is different! I even pasted the selects next to each other to make sure they were the same and hadn't changed. So why the different results?

I guess you can tell me "where to go". BigGrin
GOT ME!

Chad


Yeah, that second go screws you up with table variables. I usually use table variables for very short routines, but they are limited. First in persistence where that second go gets rid of it. The other is that a query similar to this will fail:


insert into @someTableVariable
exec someStoredProcecdure



This to me more than anything is the biggest limitation of a table variable. You can't capture values from a stored proc into it.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Judy Why
Judy Why
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1060 Visits: 828
If I ran these three SQL statements in one transaction, First returns nothing, second returns "col1".

Otherwise, both return nothing.
Sankar Reddy
Sankar Reddy
SSC Eights!
SSC Eights! (832 reputation)SSC Eights! (832 reputation)SSC Eights! (832 reputation)SSC Eights! (832 reputation)SSC Eights! (832 reputation)SSC Eights! (832 reputation)SSC Eights! (832 reputation)SSC Eights! (832 reputation)

Group: General Forum Members
Points: 832 Visits: 1250
WayneS (2/27/2009)
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?


I think the (lack of ) batch separator is causing this.
Try this:

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%'
declare @tableA TABLE(col1 int, col2 int)
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%'

In the above, although we expect to see data for only the second select, we get data from both selects.

In the below select, if you separate the select between batches then you can see the count change.

select count(*) from tempdb.sys.objects
go
declare @tableA TABLE(col1 int, col2 int)
select count(*) from tempdb.sys.objects
go

Sankar Reddy | http://SankarReddy.com/
Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 1491
If I run these statements I get zero and zero, etiher separately or as a batch.
If I add in the GO immediatly after the DECLARE, I still get zero and zero - because the table variable is no longer in scope.
If I add in place of the GO, SELECT * FROM @TableA then I get zero and 1 - the col1 in sys.columns only appears when I use the table.

Still I now know what the question was attempting to test: I found my temp table @tableA eventually and its called #32DA39A

Using SQL Server 2005, compat level 90
Dhruvesh Shah
Dhruvesh Shah
Right there with Babe
Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)

Group: General Forum Members
Points: 789 Visits: 237
Good question
khullargirish02
khullargirish02
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 126
For 2005, i get 0 rows returned for both the statements. Why?
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