SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sankar Reddy / TSQL - tempdb / Latest Posts

RE: TSQL - tempdb

khullargirish02 — Tue, 21 Jun 2011 06:49:00 GMT

For 2005, i get 0 rows returned for both the statements. Why?

RE: TSQL - tempdb

Dhruvesh Shah — Thu, 04 Nov 2010 21:01:32 GMT

Good question

RE: TSQL - tempdb

Tom Brown — Fri, 06 Mar 2009 01:40:13 GMT

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

RE: TSQL - tempdb

Sankar Reddy — Fri, 27 Feb 2009 12:13:48 GMT

[quote][b]WayneS (2/27/2009)[/b][hr]Interesting.I modified the supplied code to get the # of tempdb objects, and all tempdb table names:[code]use AdventureWorksgoselect count(*) from tempdb.sys.objectsselect name from tempdb.sys.tables --where name like '%tableA%'declare @tableA TABLE(col1 int, col2 int)--stmt 1select name from tempdb.sys.tables --where name like '%tableA%'--stmt 2select name from tempdb.sys.columns where name like 'col1%'select count(*) from tempdb.sys.objects[/code]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:[code]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%'[/code]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?[/quote]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.objectsgodeclare @tableA TABLE(col1 int, col2 int)select count(*) from tempdb.sys.objectsgo

RE: TSQL - tempdb

Judy Why — Fri, 27 Feb 2009 12:10:33 GMT

If I ran these three SQL statements in one transaction, First returns nothing, second returns "col1".Otherwise, both return nothing.

RE: TSQL - tempdb

GabyYYZ — Fri, 27 Feb 2009 08:34:09 GMT

[quote][b]Chad Crawford (2/27/2009)[/b][hr][quote][b]Gaby Abed (2/27/2009)[/b][hr]How many rows would this return? :) (yes...it's different)[code]use AdventureWorksgodeclare @tableA TABLE(col1 int, col2 int)go--stmt 1select name from tempdb.sys.tables where name like '%tableA%'--stmt 2select name from tempdb.sys.columns where name like 'col1%'[/code][/quote]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". :DGOT ME!Chad[/quote]Yeah, that second [b]go[/b] 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:[code]insert into @someTableVariable exec someStoredProcecdure[/code]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.

RE: TSQL - tempdb

Chad Crawford — Fri, 27 Feb 2009 08:28:39 GMT

[quote][b]Gaby Abed (2/27/2009)[/b][hr]How many rows would this return? :) (yes...it's different)[code]use AdventureWorksgodeclare @tableA TABLE(col1 int, col2 int)go--stmt 1select name from tempdb.sys.tables where name like '%tableA%'--stmt 2select name from tempdb.sys.columns where name like 'col1%'[/code][/quote]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". :DGOT ME!Chad

RE: TSQL - tempdb

WayneS — Fri, 27 Feb 2009 08:09:49 GMT

Interesting.I modified the supplied code to get the # of tempdb objects, and all tempdb table names:[code]use AdventureWorksgoselect count(*) from tempdb.sys.objectsselect name from tempdb.sys.tables --where name like '%tableA%'declare @tableA TABLE(col1 int, col2 int)--stmt 1select name from tempdb.sys.tables --where name like '%tableA%'--stmt 2select name from tempdb.sys.columns where name like 'col1%'select count(*) from tempdb.sys.objects[/code]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:[code]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%'[/code]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?

RE: TSQL - tempdb

TraderSam — Fri, 27 Feb 2009 06:57:29 GMT

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.

RE: TSQL - tempdb

Longy — Fri, 27 Feb 2009 06:55:06 GMT

[quote][b]Rich (2/27/2009)[/b][hr][quote][b]Longy (2/27/2009)[/b][hr][quote][b]Rich (2/27/2009)[/b][hr]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.[/quote]The answer says"Table variable names inside tempdb starts with '#' followed by [b]hex string[/b]"In which case it wouldn't find it using the name[/quote]Oops. Too early in the AM.[/quote]Yeah, I thought exactly the same thing at first, but after re-reading it spotted that!

RE: TSQL - tempdb

Source-NH — Fri, 27 Feb 2009 06:52:28 GMT

[quote][b]Longy (2/27/2009)[/b][hr][quote][b]Rich (2/27/2009)[/b][hr]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.[/quote]The answer says"Table variable names inside tempdb starts with '#' followed by [b]hex string[/b]"In which case it wouldn't find it using the name[/quote]Oops. Too early in the AM.

RE: TSQL - tempdb

GabyYYZ — Fri, 27 Feb 2009 06:47:03 GMT

How many rows would this return? :) (yes...it's different)[code]use AdventureWorksgodeclare @tableA TABLE(col1 int, col2 int)go--stmt 1select name from tempdb.sys.tables where name like '%tableA%'--stmt 2select name from tempdb.sys.columns where name like 'col1%'[/code]

RE: TSQL - tempdb

OCTom — Fri, 27 Feb 2009 06:46:16 GMT

This doesn't work on 2000 but it does on 2005.Baseball is back! :w00t:

RE: TSQL - tempdb

Longy — Fri, 27 Feb 2009 06:28:11 GMT

[quote][b]Rich (2/27/2009)[/b][hr]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.[/quote]The answer says"Table variable names inside tempdb starts with '#' followed by [b]hex string[/b]"In which case it wouldn't find it using the name

RE: TSQL - tempdb

Source-NH — Fri, 27 Feb 2009 06:17:10 GMT

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.

RE: TSQL - tempdb

TraderSam — Fri, 27 Feb 2009 05:49:18 GMT

Why would I get zero and zero when I run this set of queries on SQL 2005?

TSQL - tempdb

Sankar Reddy — Thu, 26 Feb 2009 23:40:37 GMT

Comments posted to this topic are about the item [B]TSQL - tempdb [/B]