Saturday, May 17, 2014

Back to Basics TSQL: Declaring multiple variables in one line versus individual lines.

I acknowledge that declaring multiple variables in one statement saves typing lines of code, and could save you a small (very small) amount of space in the database (if you add up thousands of stored procedures declaring variables in one statement versus on multiple lines).

I know I go against the grain for code style preferences compared to developers I've come across, as I prefer declaring variables on their own lines. The main reason is so I don't have to search through, what can be, a long list of variables to find something. Preference. The End. 

I did want to determine how SQL Server processes the differences, internally. If there was a valid performance boost to declaring these in one line, then I could be swayed to change my preference. 

I ran the following statements on my local version of SQL Sever 2014 (Developer) and watched SQL Profiler for details of "behind the scenes":

Extremely simple example:
Statement 1: Statement 1 Profiler Output:
Declaring multiple variables all on one line.








Statement 2:
Statement 2 Profiler Output:
Declaring one variable per line.


So, at least from my 5 minutes of testing, it appears as if SQL Server deems both statements as one batch.

Which coding practice should you follow? Whichever one feels right to you, because as far as performance, SQL seems to not treat them differently.

No comments: