There is a little quirk with NULLs when using the NOT IN qualifier.  I use the term "quirk" loosely here because the behavior is exactly as intended, though it may not be obvious.  The following query shows a trivial example:

 

At first glance, one might think that we'll see returned the row for the Chevy MINIVAN.  However, when you run the above query (with the default option of ANSI nulls set to ON), you'll always receive zero rows returned.  Why?  Because the NOT IN is still a set-based comparison, and with ANSI_NULLS ON, comparing any value to NULL yields False (technically it yields NULL, but you see what I mean).

A quick solution can be found, of course, by simply adding a NOT NULL to the lookup column in your subquery.  This isn't rocket science, and really isn't an advanced T-SQL topic, but it's one of those thing that can sneak up on you if you're not expecting it, particularly if you have one of these deeply nested in a complex query.

The SSIS expression language is a powerful yet enigmatic entity.  Once you get used to its syntax - which is part C#, part T-SQL and part *WTH?!?* - it's actually somewhat fun to use.  However, one thing it is lacking (as far as I can tell - correct me if I've missed something) is the ability to use an IF...ELSE IF...ELSE statement.

So I ran into a situation earlier in which I needed to do an advanced conditional expression.  Here's the situation:  My input has a column named provider.  This column comes to me as an integer but has to be padded with zeros where necessary to make it a total length of 3 ("2" becomes "002", "13" becomes "013", etc.).  A special case exception is the value "1" which is to be left as is.

I knew that the *proper* way to do this was to either create a script component and drop down into VB.NET to map the value appropriately, or use a Conditional Split transform and then merge the values together.  However, a colleague had just asked an unrelated question about the ternary operator in the expression language (officially called the Conditional Operator in Redmond), and I decided to push it a bit and force this processing into the Derived Column transform using the Conditional Operator.

So just for fun, I've posted the expression below.  It's actually three ternary Conditional Operators working together to provide an advance IF...ELSE IF...ELSE statement.  [For the record and in case it's not clear from the code below, the syntax for the Conditional Operator is (value_to_test) ? (value_to_return_if_true) : (value_to_return_if_false).]

(LEN(provider) < 3 && LEN(provider) > 0 && provider != "1") ? (LEN(provider) == 1 ? "00" + provider: "0" + provider) : (LEN(provider) == 0 ? "000" : provider)

So after many months of trudging through native SQL Server backups for new mission-critical application I'm deploying, I have decided to do some evaluations and cost/benefit analyses on aftermarket SQL Server backup products.  I've started off with SQL Backup 5 from Red Gate.  No disclaimers here - I have no affiliation with Red Gate except that they are kind enough to allow me to blog on SqlServerCentral.com (which is owned by Red Gate).

The download and installation of this product was very straightforward - the download is just under 50mb and includes the entire suite of applications on the SQL Toolbelt.  I installed only the Backup tool, which was ready for use in under 5 minutes.  The interface is very clean in intuitive, and allowed me to register my test server and view its backup history.

 To actually backup and restore on my test server (to which I am connecting remotely), I have to install a server-side component.  This process happened quickly (less than a minute), and resulted in the addition of a new service (SQL Backup Agent) on this test machine.  This service is required to schedule backup jobs from within this application, as it appears that SQL Backup 5 does not rely on the SQL Server Agent service to run scheduled backups.  Note that the server-side piece must be installed on the server even if you are not running scheduled jobs.

I ran a baseline backup using native SQL backup tools on my database.  The database, somewhere north of 26gb in size, generated a 17gb backup file in just under 1/2 hour.  Restoring this backup file to a new database took about 10 minutes.  By comparison, the same backup using Red Gate SQL Backup 5 took just over 16 minutes and created a backup file a little less than 3gb in size; restoring this file took about 6 minutes.  These operations were done using the default settings in SQL Backup 5, including a compression level of 2 (the middle-of-the-road) setting, and bypassing the Multiple Threads option.  The total time for a restore + backup operation (which will be required for our datawarehousing app) would be cut down to just over half the original time required, which was not as good as what I had hoped.  However, the size of the resulting file was less than 20% of the original size, which is right in line with my expectations.

I was interested to find that SQL Server reports the last backup date as the date and time at which I ran the backup from SQL Backup 5, which surprised me since this is a third party app and not truly integrated with SQL Server.

Licensing seems to be straightforward - the SQL Toolbelt, an all-inclusive suite of SQL Server administration tools, includes one server license for SQL Backup 5.  Additional server licenses are available as well, and licenses for test/demo installations are available at 30% of list price.  One important item of note is that the gentleman I spoke to by telephone at Red Gate indicated that we will need a license for both nodes of our SQL cluster, though the second node qualifies for the same price break as a test/demo machine.

My eval version of this product expires in a few weeks, and in the meantime I plan on pushing it further by testing some of the advanced features (changing compression levels, using multiple threads, etc.) to see how it responds.  I'm also interested in hearing from others who have used this product - please ping me or just comment here if you've had experiences, good or bad, with Red Gate SQL Backup 5.  I plan to eval at least two more products in this category as well in the coming weeks and months, and I'd appreciate any feedback.

Check out the photos from SQL Saturday 3 - Jacksonville:

http://tim-mitchell.spaces.live.com/

I just wrapped up a high-octane day of SQL Server community education at SQL Saturday in Jacksonville, Florida. This event, the third thus far in a series of training opportunities for SQL Server professionals, is loosely based on the Code Camp model and is billed as a community event. I enjoy these kind of events, having attended several Code Camps and other similar training opportunities. They are community-based, meaning that most of the trainers are peer professionals and not professional technical educators. Events are held on the weekends, and by the way, they're free to attendees.

I stepped in and did something I had never done before: I volunteered to be a presenter at this event. I've been working toward expanding my capabilities by writing and speaking more, and this was a great opportunity to do the latter (and hopefully will open some doors for the former). The session I presented addressed the SMO (SQL Server Management Objects) framework and how DBAs and developers can use these objects to automate the administration of SQL Server instances throughout an organization. As I shared with my attendees, SMO is the coolest technology that nobody is using. I've posted my notes from this session on my website, so if you're interested in learning more about SMO feel free to download the slide deck and samples and use them as you will (these will likely be posted on the SQLSaturday website as well).

I got to spend some time visiting with Andy Warren and Brian Knight about the framework of the SQL Saturday event, and I am convinced that something like this would be a big hit back home (the Dallas/Ft. Worth area). There is a large user base, and there are enough people who have demonstrated a willingness to volunteer their time to make such an event a success in north Texas. I plan to seriously pursue this so if you are interested in seeing a SQL Saturday event in the Dallas area, please let me know.

If you are a north Floridian or will be in that area next month, there is actually another SQL Saturday event coming up next month at the Orange County Convention Center. If you can make it, I encourage you to stop by.