My new favorite SQL toy - ApexSQL Refactor

Hi folks

A while ago I’ve had a blogpost about how I suffer working with unformatted SQLs.

Sometimes ago I found the tool the suits me the most - ApexSQL Refactor . It has a lot of settings, much more than all competitors I’ve tried before. I’ve managed to configure it to produce results that annoy me as little as possible. My settings can be downloaded and imported. It still has some shortcomings and hopefully they will add more features in future versions.

There are a few gotchas here though. On my current project I have to use SQL Server 2008 R2 and I kept installing this exactly version just to ensure we’re not using features from newer versions. Actually we are lucky, as before we had to support SQL Server 2005 which had even more limitations.

But recently ApexSQL Refactor dropped support for the SQL Server Management Studio older than 2012, and I was not able to download the latest version that still supports SSMS 2008. I’ve written to the tech support team and they were kind enough to send me the link for the version that works with SSMS 2008

Recently I’ve scripted and reformatted every single object from the database. Then we discovered some strange behavior in the system we are working on. After some investigation I found that actually the formatter caused it! Reformatter broke the logic.

So the problem is the following

Let’s reformat the following script

If you do that you’ll see that

IF (@@ROWCOUNT = 0) -- Username not found RETURN

SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId

is formatted as

IF (@@ROWCOUNT = 0) -- Username not found BEGIN RETURN SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId; END;

So SELECT moved from outside IF block inside it, which actually causes the problem.

But the code itself is very easy to fix, just move SELECTs out of IFs. It won’t break it again on the next reformatting because of the BEGIN/END blocks.

I’ve already written an email tech support team and they said they will reply to me shortly.

But even with this bug, I am in love with the tool!

Stay tuned!