Wednesday 1 July 2009

NULLS FIRST LAST

As I said in my previous post, I've been looking at other technologies in my continuing quest to become a better developer.

One technology is open source database called postgresql.  I haven't installed it yet just looking at the manuals and comparing to sql server.  Perhaps after the book is done I'll get a chance to play.

One of the features that I spotted that I think would pretty cool in SQL Server is the ability to specify sort order of nulls in SELECT statements.

By default in SQL Server nulls are returned first and it's a little nasty to return them last but in PostgreSQL it is easy.

SELECT Firstname, Surname
FROM Customers
ORDER BY Firstname NULLS LAST

In SQL Server you have to do some pretty nasty things that to get the same result.  Some folks often go for case statements or coalesces in the order statement to get a similar result.  I urge against this (hence why I am not showing you the code) as it produces inefficient query plans.  There are also other potential solutions that I am not going to go into (may'be another day),

My point is, I like this feature PostgreSQL has it, I think Oracle has it too and I think it might be a part of the SQL 2003 standard.  Whether it is standardized or not is a little fuzzy but I can't check as it costs lots of money to get a copy.  This leads me onto another point, the ISO standards should be free, end of story.

Finally if Microsoft are looking for a new T-SQL language feature to implement in the next version, this would be cool.

I think it meets the criteria of being something that is sufficiently hard to do and therefore should be covered in the core engine.

No comments: