Clever Geek Handbook

DECLARE after IF in TSQL - when does SQL Server allocate resources?

If I do the following:

IF @THING = TRUE BEGIN
     DECLARE @BIGTHING AS BIGINT
END

Will SQL Server allocate resources for @BIGTHING

?

Or maybe it's better to ask: does SQL Server parse the stored procedure and allocate all declared variables before executing?

+3


source to share


3 answers


It should be like

IF 1=2 BEGIN
     DECLARE @BIGTHING AS BIGINT=0
END

select @BIGTHING


works without issue for me with Microsoft SQL Server 2008 R2. Interestingly, however, if the default variable has a value as shown above, it only sets the value if the IF condition is true.

+6


source


A variable is available for use in a T-SQL batch from the point at which it is declared to the end of the batch. This means that a) variable declarations do not participate in any form of control flow, and b) variable names must be unique within the batch, even if they appear to be in mutually exclusive parts of the batch. SpectralGhost's answer shows the implications of this.



+2


source


Distribution is not the right word for him. All local variables only set slots on the stack as part of the function declaration. It took some time to zero out the values, but this is probably done en masse for the entire stack frame.

I wonder when the code is recompiled. This can happen in the middle of a stored procedure for a variety of reasons. The one I was told specifically to look at is changing the temporary table. If you make any changes to the temp table, a new execution plan will be created for the rest of the saved process.

0


source






More articles:


All Articles