SQL Short Circuit Mechanism and OR

Short circuit works in sql much like any other logical system. Given a sequence of expressions, connected by an OR operatorthe first expressions to evaluate as true, from left to right, exits the group as true.

As an example:


Where (@var1>2 or @var2<=15 or s.column=@var3)

The above statement will return true since @var1 is true regardless of the values of @var2 and @var3. This is important to realize since sql will not adjust the order of your expressions if they are part of an OR operation. Above you would prefer to make sure @var1 or @var2 fails before running the compare with the table, which is why I put them first.

Left-padding a column

I have had many situations where there needs to be a left padded column in sql. Usually it's a number that needs to have pre-pended 0s. Here is a very simple way. I will list the formula and then give an example:

select right('<X>0'+cast(@var as varchar(X)),X)

The bold sections (the and the number of 0s) need to be the same. So given the above formula, if I need a column padded out 10 places, I have 10 0s which means the max size of the field will be 10 and I need to make both Xs 10:

Division of Labor; SSIS and Sql Server

SSIS was born for parallel processing, while sql server wasn't; well not from the users perspective. Yes, sql server breaks a user's query into parallel processes, but there is no way I can 'fork' within a stored procedure, or do an asynchronous call. I originally designed a process which I outline here "Parallel Processing in SQL"which works beautifully if in fact the work to be done is broken into equal parts.

Parallel processing in SQL Server

There have been manyoccasionswhere I've needed to run multiple copies of the same process for performance reasons. Let's say for example that a stored procedure can process 10 records/sec, but I have 1,000,000 records to process. It would take 27.7 hours to process at the previous rate. If I could break up this process into discrete pieces I could run multiple copies and reduce the total run time in a linear fashion (that is if it takes 10 hours to run 1 copy, it would take 5 hours to run 2 copies...1 hour to run 10 copies). There is a computer operation known as, Modulo.