Unified ID’s for legacy database

I found a handy T-SQL trick that helped to solve the following problem: during datawarehouse creation, we needed to consolidate multiple data sources from databases belonging to two generations of system: older one using int identity keys, the newer one, designed for replication and distributed data scenarios using GUID’s as primary keys.

In the consolidated database, we needed to create single representation that would allow to store both new GUID based identities as well as old int-based keys. Direct conversion between UNIQUEIDENTIFIER type and int is not allowed. The following one liner will do the trick:


CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY(10), 0) + CONVERT(BINARY(6), @id))


The “fake GUID’s” it creates from integers are easy to identify as they have leading zeros. If you for any reason do not like it, replace the 0 in first convert with any suitable number.

Differences between Visual Studio and SSMS

I am working on the BI modules for automotive data-analytics application these days. Which means that I am doing quite a bit coding and using T-SQL and in addition to Visual Studio 2005, I use quite often the Sql Server Management Studio for SQL Server 2005. In theory, both tools should be based on the Visual Studio IDE code base. Which is maybe true, but there are many quite annoying differences.

Some of the differences is available featureset. I wish there was single tool that had all the database features of both: strong scripting capabilities of SSMS and much better development support with debugging in VS-2005. One can understand that the tools were meant to serve different audiences, but why not make the SSMS a lightweight, trimmed down version of VS2005 targeting database and OLAP projects only ?

Some differences are really annoying though. One discovered today is related to GUID’s. Take the code sequence

Declare @MyGuid uniqueidentifier
set @MyGuid = NEWID()
select 'aaaaa', @MyGuid

You would expect that the result will be one row, consisting of value ‘aaaaa’ and some GUID, right ? Wrong. It is true only in SSMS as the picture shows:

In Visual Studio 2005 SP1, the result looks like this:

which on one side announces 1 row in resultset, but does show the same result as if the resultset was empty. As we found out, the problem is related to displaying the GUID value – if your resultset does not contain it, the row will show OK. Same result can be achieved using explicit conversion, as shown in the picture:

Best book for Ruby on Rails

During last two weeks I have been reading and playing a lot with Rails.  Now I am in the state where I think I started to “get it” and understand how things work under the surface. Which means I am ready actually start using the framework for real work and become proficient as I get my hands dirty.

To get to this state, absolutely essential and by far the best book is the classic one – Agile Web development with Rails co-authored by the original Rails designer and one of the Pragmatic Programmers. Like other books, the first part is sort of step-by-step code tutorial, which leads you through creation of a Rails application, shows you how you access data, create UI, use scaffolding, etc etc. Unlike other books, this does not stop there and the second part of the book is an excellent coverage of Rails internals – ActiveRecord, ActiveView and templating system. Very well written and offering great balance between covering important features without becoming reference manual.

The best feature of this and other Pragmatic books is that you can purchase it as e-Book in the PDF format (without any DRM restrictions, just customized with  your name in text) which is cheaper, very portable 🙂 and very search-able. As added bonus, you get free updates when Rails changes or an error is discovered. As matter of fact, I just got such email, only few days after I bought the book.

The above book is not the only e-book I have purchased from Pragmatic Programmers. After 9 years, I have decided to buy a book covering such basic and boring thing as an editor. My previous (and first ever) book focusing  on how to use an editor was “Learning GNU Emacs“, back in 1998. You may say that if you need a book to use an editor, there must be something wrong with that piece of software. That is certainly one possible view. But the other explanation is that the editor is so powerful, extensible, customizable that it allows many ways how to be used and deserves the book. In my opinion, the later is very true about Emacs. I was using it for few years, but eventually switched back to Windows alternatives or IDE’s such as Eclipse or Visual Studio.

Now there seems to be another editor that has promise to be such great, powerful and  extensible tool as Emacs was  – but different way. It is TextMate – an editor available only on OS-X platform. I have got the license as part of the Mac Heist charity sale over Christmas and so far I was only scratching surface of its capabilities by using it similar way how I use Notepad++ on Windows. So I have decided to spend 20 bucks and few hours to “unlock the power” – editor is such basic tool used for hours every day that improving and optimizing your work habits actually can make a huge difference. Besides, TextMate is “the editor” for Rails development on OS-X and I am now in “wow-phase” of discovering the power of bundles. If you are on a Mac, give it a try – it is worth it.

Accessing SQL Server 2005 from Ruby

The natural gravity of our environment keeps pulling me out of scripting and Ruby world back to .Net zone :-). Out of curiosity, I looked into how easy or hard is it to access other database from Ruby, than the default MySQL which dominates all books, samples and tutorials.

I have found three ways (at least), one likely platform independent and two Windows only. The platform independent requires module ADO.rb and works with the DBI interface. This sounds very similar to the way how Perl used to access databases, so I put this at the end of the list (some details are here). First Windows only methods used ODBC – no, thanks. Configuring machine specific data sources is not the way I wanted.

The best way was described in the Dave Mullet’s blog and worked perfectly. I took the liberty to make the class better configurable – the result is published at code snippets.

If you do not know DZone Snippets, give it a look – it is best way how to share chunks of code that are too big to fit into blog (like one-liners or small functions) but not really a projects, which could be uploaded to SourceForge or CodePlex or similar open source repository. Snippets have coloring filter and tagging system – and the site is YACLRRW2C – yet another cool looking Ruby on Rails Web 2.0-ish creation …

  

Handling concurrency in SQL Server applications

On Tuesday evening, the Ottawa Dotnet Users Group organized presentation of Adam Machanic from Boston with title “Designing Highly Concurrent SQL Server Database Applications”. I was not sure whether I will make it, because because I did not feel really well, but I am very happy I went.

It was probably one of the best presentations I have seen in Microsoft Glacier Room on 100 Queen Street. I am not sure whether because it was indeed so great or because it touches closely what we are doing.

What was great on the lecture was lack of dependency on SQL server. Technically, most modern databases could be used and the techniques would still work. We are going to stay with SQL 2005 which is btw one very decent, well performing, easy to install and manage database system – but learning something portable is always a pleasure.

The presentation covered two classical way how to handle concurrency – pessimistic and optimistic and presented quite clever way how to do it without stored procedures. It also covered the “multi value” approach, which replaces update with inserts and maintain multiple copies of each record. This is btw, very close to what we are doing in Tirevigil Navigator – keeping full history of tire and vehicle data.

Adam is an co-author of Pro SQL Server 2005 as well as blogger: see this and this . His new book Expert SQL Server 2005 Development is about to be published in March – I will definitely give it a serious look.
You can grab the slides and source code from the presentation here.