This either used to be true a long time ago (and no longer is, at least since SQL Server 2000), or it never was true and your DBA just confused his recommendation with the following one:
It is important to group together all DDL statements (like creating indexes) for temporary tables at the start of a stored procedure. By placing these DDL statements together unnecessary compilations due to schema change can be avoided.
You can find another explanation of the reasoning behind this recommendation on this page.
If we take a look at this Microsoft KB, we see that the cause of a stored procedure recompile can be one of the following (SQL Server 2005+):
- Schema changed.
- Statistics changed.
- Recompile DNR.
- Set option changed.
- Temp table changed.
- Remote rowset changed.
- For browse perms changed.
- Query notification environment changed.
- MPI view changed.
- Cursor options changed.
- With recompile option.
Declaring a variable -- even a table variable (i.e.
@table_variable) -- cannot trigger any of these events, obviously, because declaring a variable doesn't count as DDL. A variable (even a table variable) is a temporary object used exclusively for your T-SQL programming. That's why table variables get no statistics and are not bound by transactions. Declaring a variable (table or not) cannot trigger a proc recompile.
Creating a temp table (i.e.
#temp_table) or an index, however, is DDL that affects the physical definition of the database. Temp tables and indexes are "real" objects with statistics and transactional control, therefore creating them could fire any of events 1, 2, or 5 in the list above and thus trigger a proc recompile.