Did you know that EXISTS... exists?

One of the most useful and powerful functions that Microsoft SQL Server provides, is the EXISTS function that checks for the existence of records in a specific query. Unfortunately it is not used as often as it should be, mainly because a lot of SQL developers come from structural and/or object oriented programming, having difficulty to grasp SQL concepts.

The most common bad practice is the use of COUNT for checking the existence of records. For example:

IF (SELECT COUNT(*) FROM Persons WHERE LastName LIKE 'Smith') > 0
   SELECT 1
ELSE
   SELECT 0

The above query checks the existence of persons whose last name is "Smith". In order to do so, it first gets the count of all the persons named "Smith", and then check is the count is greater than zero. Though it's logically correct, it's pretty far from optimal. The equivalent query with EXISTS is:

IF EXISTS(SELECT * FROM Persons WHERE LastName LIKE 'Smith')
   SELECT 1
ELSE
   SELECT 0

This query checks for the first occurrence of a person named "Smith", and if it finds one, it returns TRUE. You can understand that it's potentially a lot faster than the previous query, since it only checks for one record, and doesn't try to count all the records with the specified criteria.

Another great use of EXISTS, is for eliminating records instead of using INNER JOIN. It's quite a common practice to use INNER JOIN to filter out records, while not using any of the joined table's fields in the SELECT list. All those queries can benefit from EXISTS. For example:

SELECT DISTINCT
   p.ID,
   p.LastName,
   p.FirstName
FROM
   Persons p
INNER JOIN Contracts c
   ON c.PersonID = p.ID

The above query fetches all the persons that have at least on contract, and we can see that there is no field from Contracts table in the SELECT list. It also uses DISTINCT, which is quite heavy on the server. The equivalent query with EXISTS is:

SELECT
   p.ID,
   p.LastName,
   p.FirstName 
FROM 
   Persons p
WHERE
   EXISTS(SELECT * FROM Contracts c WHERE c.PersonID = p.ID)

With this query, we don't have to use DISTINCT, and its performance is much much greater than the previous query, since it only checks for one record in the Contracts table instead of finding all of them.

Though these cases may sound simple and unrealistic, they appear a lot in SQL queries, whether they are used frequently or on demand. So, next time you need to write a simple query for checking the existence of a record, or a complex one that comprises many criteria in different tables, be sure to remember to use EXISTS, and I'm sure that you will be pleasantly surprised by the results!

WinForms are dead... long live WinForms!

After Microsoft's introduction to WPF, a lot of .NET programmers started to announce "the death of WinForms". But is that really true?

WinForms is a GUI API that exists since the beginning of the .NET framework, it is almost identical to the MFC C++ GUI API, and it is immensely similar to other GUI APIs of other programming languages (AWT - Java, SWT - Java, Qt -C++, wxWidgets - C++). It's quite easy to learn, a lot easier to understand its concepts, and it can help you move to other languages' APIs. Yet, Microsoft decided to abandon its development in favor of the WPF API. So one would expect a lot from WPF. But is that the case?

WPF is the new GUI API from Microsoft, that makes a better use of newer technologies in graphics rendering (DirectX instead of GDI) and tries to enforce the separation of the UI logic and the business logic in an application. Sounds really great, right? Well, lets see the way it does it. WPF somewhat resembles HTML, it uses a descriptive language (XAML), which is an XML-ish format that describes the UI elements and also allows developers to do inline data bindings in UI controls, which then renders it on the screen, using DirectX subsystem. Unfortunately, Visual Studio's WPF designer is not really easy to use, unlike its WinForms designer which is really a breeze, XAML is not as easy as WinForms in constructing a complex form consisting of a lot controls and events, and in general WPF is a lot more difficult to learn and master than WinForms. Also, though one would expect a better performance in GUI rendering due to its use of DirectX subsystem, unfortunately that's not the case, especially in older PC configurations. So why use WPF at all?

WPF allows more freedom to the developer in creating UI elements, it does make the code cleaner and neater, and it is constantly being updated with every .NET framework version update. But why oh why, did Microsoft stop updating WinForms? A lot of existing enterprise solutions are based exclusively on WinForms, they are faster, easier to learn and allow extremely rapid development. I guess it's due to Microsoft's desperate attempt to promote its new ecosystem of Operating Systems, Windows 8 and Windows Phone 8, and increase the count of applications in its AppStore.

I personally believe that Microsoft did not think this through at all, it made a lot of developers to switch sides, and most Windows users refused to embrace its new philosophy, staying in older versions of Windows. Fortunately, latest announcements for Windows 8.1 Update 1 and Windows 9, seem to try and bridge the gap that Windows 8 created. My personal opinion is that Microsoft should start giving a little more love to WinForms, accepting the fact that developers are and will develop in WinForms. Making use of the DirectX subsystem, enhancing the existing controls and making better use of databinding would be a start.

Rant's conclusions:

WPF should be a great decision for developing applications that target users with newer PC configurations and newer Windows versions, and rely mostly on eye candy GUI.

WinForms should be a great decision for extremely rapid development, and for developing applications that have a more general target group of users, and rely on maintaining high compatibility for most Windows versions.

Introduction

This is a blog of a time long ago. A time of myth and legend, where...
Nah, scratch that...

This is a blog with my rants about coding (mainly .NET and PHP), SQL, operating systems, enterprise decisions, etc...

Hope you enjoy those rants and maybe add some of yours at the comments section. ;)