batch, level, call stack

The following code demonstrates that local temporary tables are visible across batches in the same level in addition to in inner levels in the call stack. The code runs successfully without any errors.
USE TSQL2012;
CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
EXEC('SELECT col1 FROM #T1;');
GO
SELECT col1 FROM #T1;
GO
DROP TABLE #T1;

Open in new window


My question is about defining the following phrases:
Q1: What is level here?
Q2: What is batches here?
Q3: What is inner levels here?
Q4: What is  call stack here?

I have some understanding of the above but I want to double check them to make sure.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
1. CREATE TABLE (see Temporary Tables section)

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions.

The word "level" is a bit suspect.

2. GO (see Remarks section)

3. Scope (see EXECUTE).

4. ??? Call stack makes no sense to me. Maybe just the order. Or they are talking about nested transactions.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
The original description was (from a book):
The following code demonstrates that local temporary tables are visible across batches in the same level in addition to in inner levels in the call stack.

My focus here is to understand the words in bold.

Q1. level: What is level in the code below? How many of them are there and what are they?
Q2. batches: It is easy, for example, Use Db1; is a session and Use Db2 ; is another session.
Q3. inner level: What is inner level in the code below? How many of them are there and what are they?
Q4. call stack: What is level in the code below? Vow many of them are there and what are they?

On Q4, I think the followings are call stack but I am not 100% sure:
call stack 1:  USE TSQL2012;

call stack 2: CREATE TABLE #T1

call stack 3: INSERT INTO #T1(col1) VALUES(10);

call stack 4: EXEC('SELECT col1 FROM #T1;');

call stack 5: SELECT col1 FROM #T1;

call stack 6: DROP TABLE #T1;

Please help.

Thanks,

Mike

Once again the code is:
USE TSQL2012;
CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
EXEC('SELECT col1 FROM #T1;');
GO
SELECT col1 FROM #T1;
GO
DROP TABLE #T1;

Open in new window

0
Scott PletcherSenior DBACommented:
Q1.  Level = @@NESTLEVEL.  When a proc or CLR routine is called, that is a new level.  So, if Proc A execs Proc B, Proc B increments the level, and it can see temp tables created by Proc A, but Proc A cannot see temp tables created by Proc B.

Q2.  Depends on the specific environment, but for SSMS, "GO" is the batch separator.

Q3.  "Inner level" = higher @@NESTLEVEL.

Q4.  A traditional computer stack that SQL uses to return you to the caller.  In general terms (not in technical, specific detail), say Proc A calls Proc B which calls Proc C.  Proc C is on the top of the stack, Proc B is next, and then Proc A.  That way, SQL can determine where to return after a call to other code.  Likewise with functions and even ad-hoc SQL, they are stacked to allow a return to the right place in the calling code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Scott,

I had mixed up the definition for session with batch for a moment. Thank you for clarifications.

I suppose, the books definition followed by the submitted code is a bit strange where the terms like lever, inner level, or call stacks could not be pointed out in the sample code. I hoped to related the terms by their use example in the sample code. I suppose it doesn't apply.

But your definition, relating to some other use areas, were helpful. Now I see that what we have here is different batches (in  the same level in addition to in inner levels in the call stack). So I shouldn't be looking for them individually.

Thanks,

Mike

This post has been revised.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.