Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 GO and batches

Author  Topic 

sehnsucht
Starting Member

8 Posts

Posted - 2008-08-15 : 07:13:28
I'm a little confused. I was doing a few experiments to prove to myself that table variables in sql server 2000 do involve a write to tempdb. I wrote the following code:

use tempdb
go

select COUNT(*) from INFORMATION_SCHEMA.TABLES
go
declare @mytemp TABLE(somecol INT)
select COUNT(*) from INFORMATION_SCHEMA.TABLES

The first GO is just a standard thing I always do after USE. I don't see why I need the second GO though. To my mind, the select should return a value (2 on my server at the mo - it'll obviously vary depending on what's going on on the server though), then the declare should cause the creation of another row, giving a value of 3 from the second select. But if I miss out this second GO then I get 3 returned both times - it's as if both selects are run together after the declare. I don't understand this at all - can anyone explain?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 07:20:36
A "GO" statements clears the current batch, thus clearing table variables.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sehnsucht
Starting Member

8 Posts

Posted - 2008-08-15 : 07:55:06
That doesn't address my question at all.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 07:59:35
Why do you think so?
In TempDB even worktables (such as intermediate worktables and table variables are stored by name).




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 07:59:57
Not helpful Peso,

He's asking why his first select statement (before he's declared his table variable) still brings it back (before it has actually been created). (sehnsucht, if you do a select * you'll see a #table with a name that is just a string of hex digits. That's the table you are making with your DECLARE @ TABLE syntax.)

Note - this doesn't happen if you create a #table, you'll get different counts

-------------
Charlie
Go to Top of Page

sehnsucht
Starting Member

8 Posts

Posted - 2008-08-15 : 08:06:02
quote:
Originally posted by Transact Charlie

Not helpful Peso,

He's asking why his first select statement (before he's declared his table variable) still brings it back (before it has actually been created). (sehnsucht, if you do a select * you'll see a #table with a name that is just a string of hex digits. That's the table you are making with your DECLARE @ TABLE syntax.)

Note - this doesn't happen if you create a #table, you'll get different counts

-------------
Charlie



Thanks. I do understand about the #table with the hex digits. That isn't what's confusing me - what's doing that is the "time travel" that's going on when the second GO is removed.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 08:09:47
Well I guess inside the batch, the server must look at the whole batch before doing anything and then allocate space for any declared tables this way. However, I didn't know it did this before your post. Because both select statements are inside the same batch with the deceleration they both execute after space has been allocated.

-------------
Charlie
Go to Top of Page

sehnsucht
Starting Member

8 Posts

Posted - 2008-08-15 : 08:15:06
quote:
Originally posted by Transact Charlie

Well I guess inside the batch, the server must look at the whole batch before doing anything and then allocate space for any declared tables this way. However, I didn't know it did this before your post. Because both select statements are inside the same batch with the deceleration they both execute after space has been allocated.

-------------
Charlie



It's odd, isn't it. We've both learnt something! Using a temp table as below works as "expected":

use tempdb
go
select COUNT(*) from INFORMATION_SCHEMA.TABLES
create TABLE #mytemp (somecol INT)
select COUNT(*) from INFORMATION_SCHEMA.TABLES
go
drop TABLE #mytemp
go


(this returns 2 and 3).

Cheers.

Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 08:19:06
I'm sure some guru here will know everything about this.

-------------
Charlie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 08:42:09
quote:
Originally posted by Transact Charlie

... (before he's declared his table variable) still brings it back (before it has actually been created).

Why do you think the table variable is not created first?

Try this example
IF 1 = 1
CREATE TABLE #Temp (Yak INT)
ELSE
CREATE TABLE #Temp (SQLTeam MONEY)

DROP TABLE #Temp
Now tell me if tables are created on the fly when told by code, or at compile time?
As you can see by the code, both CREATE TABLE cannot happen right?

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 08:43:26
Or this example with table variables?
IF 1 = 1
DECLARE @Temp TABLE (Yak INT)
ELSE
DECLARE @Temp TABLE (SQLTeam MONEY)

As you can see by the code, both DECLARE TABLE cannot happen right?

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 08:47:02
Even simpler. The same thing applies to variables.
See
IF 1 = 1
DECLARE @Yak INT
ELSE
DECLARE @Yak MONEY
As before, both DECLAREs cannot happen, right?
But still the code yields an error.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 08:51:46
And now back to the original query
select COUNT(*) from tempdb.INFORMATION_SCHEMA.TABLES
--go
declare @a TABLE (somecol INT)
select COUNT(*) from tempdb.INFORMATION_SCHEMA.TABLES
go
At compile time, the @a table is created.
Then you get the count from information_schema.tables.
Then you get the count from information_schema.tables again.

And you have the same count, right?

Now add a "GO" statement.
select COUNT(*) from tempdb.INFORMATION_SCHEMA.TABLES
go
declare @a TABLE (somecol INT)
select COUNT(*) from tempdb.INFORMATION_SCHEMA.TABLES
go
At compile time, the @a table is created.
Then you get the count from information_schema.tables.
Issue a "GO".
Then you get the count from information_schema.tables again.

Now you don't have the same count, right?
This is because the "GO" clears all variables, even table variables.

And the other issue, the same code with temp tables.
Temp tables are NOT deleted or purged with a "GO" statement.
Temp tables are only automatically dropped when connection is lost, not when a "GO" is issued.

quote:
Originally posted by Transact Charlie

Not helpful Peso

quote:
Originally posted by sehnsucht

That doesn't address my question at all.

I hope this explanation is satisfying for both of you.
A "GO" statements clears the current batch, thus clearing table variables.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 08:58:32
Well OK,

For example I've got this code. It performs a query to information_schema.tables and then outputs the result as quickly as it can, (via raiserror).

It then waits 5 seconds and then declares the table (which is included in the count above). Try it.

If you replace the table variable with a regular temp table it doesn't do this.

N.B : you can't reference the table variable in any way until you declare it so the results from INFORMATION_SCHEMA.TABLES do look kinda weird unless the batch is being pre-scanned and all table variables cached before any sql is executed.


USE tempDb
GO

DECLARE @stop CHAR(9)
DECLARE @output VARCHAR(255)

SET @stop = '00:00:05'

SELECT @output = CAST(COUNT([table_name]) AS VARCHAR(20)) FROM INFORMATION_SCHEMA.TABLES WITH (NOWAIT, NOLOCK) WHERE [table_Name] LIKE '#%'
RAISERROR(@output, 0, 1) WITH NOWAIT

WAITFOR DELAY @stop

DECLARE @myTableA TABLE (field1 INT)



-- EDIT -- By weird I didn't mean that sql server is doing something in this case it's not supposed to do, but is doing something in a way I didn't expect.

-------------
Charlie
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 09:04:02
+ I'm sorry if I offended you, I didn't mean to.

I agree with you and did understand that at the end of a batch, the scope is destroyed and all variables are lost (including table variables) However, I didn't know there was a difference in the way that the contained is created between temp tables and table variables. Which is why I assume (as I think OP did) that the container for the table variable would not get created until the DECLARE statement was reached (by going statement by statement into the batch).

It is obviously created first (As you said at compile time).

Apologies again.

-------------
Charlie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 09:15:32
[code]DECLARE @Tables INT

SELECT @Tables = COUNT(*)
FROM TempDB.INFORMATION_SCHEMA.TABLES WITH (NOWAIT, NOLOCK)
WHERE TABLE_NAME LIKE '#%'

RAISERROR('Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is %d.', 10, 1, @Tables) WITH NOWAIT

WAITFOR DELAY '00:00:05'

DECLARE @myTableA TABLE (field1 INT)

SELECT @Tables = COUNT(*)
FROM TempDB.INFORMATION_SCHEMA.TABLES WITH (NOWAIT, NOLOCK)
WHERE TABLE_NAME LIKE '#%'

RAISERROR('Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is %d.', 10, 1, @Tables) WITH NOWAIT[/code]Output is[code]Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is 3.
Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is 3.[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 09:19:02
[code]
DECLARE @Tables INT

SELECT @Tables = COUNT(*)
FROM TempDB.INFORMATION_SCHEMA.TABLES WITH (NOWAIT, NOLOCK)
WHERE TABLE_NAME LIKE '#%'

RAISERROR('Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is %d.', 10, 1, @Tables) WITH NOWAIT

WAITFOR DELAY '00:00:05'

CREATE TABLE #a (field1 INT)

SELECT @Tables = COUNT(*)
FROM TempDB.INFORMATION_SCHEMA.TABLES WITH (NOWAIT, NOLOCK)
WHERE TABLE_NAME LIKE '#%'

RAISERROR('Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is %d.', 10, 1, @Tables) WITH NOWAIT
[/code]
Output is :
Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is 20.
Number of temp tables and table variables in INFORMATION_SCHEMA.TABLES view is 21.

Which is why I was surprised. I thought there would be no difference between the way a table variable is allocated space and the way a temp table is.

-------------
Charlie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 09:21:19
quote:
Originally posted by Transact Charlie

It is obviously created first (As you said at compile time).

Apologies again.
Thank you. You're welcome.
I hope you learned something from this topic.
T-SQL is not like a modern programming language which has JIT built-in.

And for T-SQL it makes sense that the tables are created first at compile time.
Then the query engine has a better foundation how to calculate the execution plan.

Think of the table wasn't created untíl code told it to. Like the IF statement I posted above.
It would be much harder for the query engine to decide best approach.

And now think about dynamic sql! How many topics here have you seen where OP wants to create columns and even tables dynamically...
What then happens to the query plan and future JOINS further down in the code?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 09:25:46
quote:
Originally posted by Peso

quote:
Originally posted by Transact Charlie

It is obviously created first (As you said at compile time).

Apologies again.
Thank you. You're welcome.
I hope you learned something from this topic.
T-SQL is not like a modern programming language which has JIT built-in.

And for T-SQL it makes sense that the tables are created first at compile time.
Then the query engine has a better foundation how to calculate the execution plan.

Think of the table wasn't created untíl code told it to. Like the IF statement I posted above.
It would be much harder for the query engine to decide best approach.

And now think about dynamic sql! How many topics here have you seen where OP wants to create columns and even tables dynamically... What then happens to the query plan?



E 12°55'05.25"
N 56°04'39.16"




Yes that all make good and logical sense.

What explains the difference in counts from your query between #tables and table variables?

-------------
Charlie
Go to Top of Page

sehnsucht
Starting Member

8 Posts

Posted - 2008-08-15 : 09:26:45
quote:

I agree with you and did understand that at the end of a batch, the scope is destroyed and all variables are lost (including table variables) However, I didn't know there was a difference in the way that the contained is created between temp tables and table variables. Which is why I assume (as I think OP did) that the container for the table variable would not get created until the DECLARE statement was reached (by going statement by statement into the batch).



I have a book on SQL at home (by Ken Henderson, I believe) where he mentions something along these lines, although it was obviously either a different example or I read it long enough ago for it to not ring a bell this morning when I had this problem. I'll try and dig it out over the weekend in case it's relevant.

Thanks for the discussion - it's been interesting.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-15 : 09:29:30
my thoughts exactly. It's stimulated my late lunch slump!

-------------
Charlie
Go to Top of Page
    Next Page

- Advertisement -