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 operator, the first expressions to evaluate as true, from left to right, exits the group as true.

As an example:

@var1=5,@var2=20,@var3='Live'

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.

SQL Output to an Email Attachment

This is a quick example of how to take the output of a sql query and email it to someone.

The above image shows a few of the pieces.  Create a variable, in this case filename.  You then open the properties editor for the sendmail task.  You select fileAttachments and link the variable.  This will send the text file you create as an attachment.  Now we go to the DataFlow task to see how to connect the output of your sql to that same textfile.

You create a DataReader source and connect the output to a FlatFile destination.  To create the FlatFile destination you need a FlatFileConnectionManager and that is where you connect the output to a physical file.  Click on your FlatFileConnectionManager then on the properties window on the right, click on the  + sign to the left of Expressions.  Click on the ellipses (...) on the right side, which will open the PropertiesExpressionsEditor you see on the left.  Select the ConnectionString property and select the filename variable.  

You have now connected the output of your sql to the file attachment in your email. 

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 X 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:

select right('0000000000'+cast(@var as varchar(10)),10)

If the max column size is 4, the above formula would be:

select right('0000'+cast(@var as varchar(4)),4)

If you need to do this in sql 2000 simply replace the cast with a convert:

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

SQL 2005 Trace flag 2301

I tried to use this traceflag after reading the High Performance article from microsoft.  I find it very useful on individual queries where you are accessing large tables.  I have a base table of just over 320 million records and under certain circumstances I have seen improvements of over 1000%

SQL 2005 High Performance Workload Tuning

This is a nice page from microsoft with numerous traceflags used for squeezing the last bit of performance out of sql 2005.  TraceFlags