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

---

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

---

quote:

---

Originally posted by Transact Charlie

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

---

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

DROP TABLE #Temp

IF 1 = 1
	DECLARE @Temp TABLE (Yak INT)
ELSE
	DECLARE @Temp TABLE (SQLTeam MONEY)

IF 1 = 1
	DECLARE @Yak INT
ELSE
	DECLARE @Yak MONEY

select COUNT(*) from tempdb.INFORMATION_SCHEMA.TABLES
--go
declare @a TABLE (somecol INT)
select COUNT(*) from tempdb.INFORMATION_SCHEMA.TABLES
go

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

quote:

---

Originally posted by Transact Charlie

Not helpful Peso

---

quote:

---

Originally posted by sehnsucht

That doesn't address my question at all.

---

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)

quote:

---

Originally posted by Transact Charlie

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

Apologies again.

---

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"

---

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).

---