Array Traversal i++ vs i--

Array Bounds-Check Removal

One of the optimization done in .NET CLR is removing bounds-check when accessing arrays. It's done in certain cases and this excellent blog post discusses these cases (http://blogs.msdn.com/b/clrcodegeneration/archive/2009/08/13/array-bounds-check-elimination-in-the-clr.aspx)

Something that I found really interesting is that it matters as far as bounds-check elimination is concerned whether arrays traversed in an increasing or decreasing order.

for(var i=0;i<someArray.Length;i++){ //array access using i} vs. for(var i=someArray.Length-1;i>=0;i--) { //array access using i }

 So in accessing the array within with the first loop, the bound check is eliminated but in the in the second case (decreasing; i--). The mentioned posts recommend traversing the array in ascending order whenever possible.

Another useful advice from the post is that using the Length property in the condition statement of a for loop is better than using a local variable (unless the local variable is intended to access a subset of the array of course). Interesting stuff.

 

 

 

 

Generate SQL to Search All Columns in a Table

If you are searching for a given value in a table with numerous columns not knowing which column could match the value, you'll find it tedious to write as many OR clauses as columns in your table.

Here is a simple T-SQL script to generate SQL code with all the column names and the OR clauses.

 declare @sql nvarchar(max);
 select @sql = 'select * from MY_TABLE_NAME where ';
 select @sql = @sql + c.name + ' = ''value'' or '
 from sys.tables t 
  inner join sys.columns c on t.object_id = c.object_id 
  and c.system_type_id in(231,167) --this restricts target columns to just nvarchar,varchar types
  and t.name = 'MY_TABLE_NAME';
  
 select @sql = left(@sql,len(@sql)-3)+';'; -- remove the last or
 print @sql;

MY_TABLE_NAME has to be changed to your specific table of course. This script can be improved to add conditional casting. When the column type (system_type_id) is string then no casting is needed but when integer then convert to varchar and when the type is datetime for example then use convert(varhcar(10),101) for example.