Archive for June 16th, 2010

DECLARE with Initializer June 16th, 2010

Vinod Kumar

In SQL Server 2008, the DECLARE statement has been extended to allow specifying an initial value along with the declaration. Not sure how many of you have taken a chance to use this feature … Beyond the normal looks there is more to this than the eye can read, typical example is –

declare @var varchar(30) = ‘Hello’

This is very much to the tone of declaring and then issuing a SET command like –

declare @var varchar(30)
Set @var = ‘Hello’

Interestingly, this might not be the case. Note that even though the value is specified in the DECLARE it is not a parse/compile time operation to initialize it. The initialization to that value still happens at execution time. On previous versions of SQL Server, the variable was implicitly initialized to NULL and the DECLARE statement didn’t actually have an execution component. That is, if you traced the SQL:StmtStarting or SQL:StmtCompleted events there was no event produced for the DECLARE statement.

When you use this construct in SQL Server 2008, the value is still implicitly initialized to NULL at compile time and the variable’s value is assigned at runtime, which internally is exactly the same as what you would get for a SET statement initializing the variable. But now you will start seeing this part of Profiler if used with initialization.


There is surely an implicit conversion that occurs as part of the initialization incase you dont use the right castings and now DECLARE statement also must be part of the try-catch block because of this very reason :) …

The best part doesnt end here, the optimizer still treats the variable’s contents as unknown (NULL) for cardinality estimation:

declare @state varchar(2) = ‘CA’
select * from authors where state = @state    — card estimate is 2.875
select * from authors where state = ‘CA’    — card estimate is 15

Just check on the “Estimated number of rows” column and these values be seen … Though some of these features look so trivial, there are tons of interesting behaviors these features open. So feel free to look at these features closely and enjoy the usage with deeper understanding …

Continue reading...