mandag den 29. juli 2013

SQL Server: Common Table Expressions

One nice thing I stumbled upon reading the "Querying Microsoft SQL Server 2012" book is a feature called "Commen Table Expressions" or CTE's.

As it turns out the feature was already available in SQL Server 2005. If other people than me managed to be ignorant of its existence then let me try to explain what it is about.

The basic form of a CTE is this:
WITH <CTE_name> AS
(
   <inner_query>
)
<outer_query>

This form allows us to assign a name (CTE_name in the above) to a query and then use that in the <outer_query>.

Of course, this is just different kind of view or subquery (derived table). It allows one to avoid creating permanent database objects (as with views). It it also arguably more readable than named subqueries and - more importantly - avoids the need to repeat the definition of a subquery as is sometimes necessary.

Consider this example:
select ...
from (<subquery>) T1
inner join (<subquery>) T2 on ...

When self-joining the <subquery> one must repeat the defintion of the <subquery>, which is of course redundant.

For any functional programmers out there, CTE's play the role of let-expressions.

Recursive CTE

Nice as it is, the above form of CTE is mostly syntactical sugar, but there is another form of CTE that does something entirely different and makes it easier and faster to deal with hierachies - something that has always been notoriously difficult in SQL.

Enter the recursive CTE:
WITH <CTE_name>
AS
(
   SELECT col_1, ..., col_n
   FROM <table_expression_1>

   UNION ALL

   SELECT col_1, ..., col_n
   FROM <table_expression_2>
)
<outer_query>
This is interesting because the <table_expression_2> is allowed to refer to CTE_name.What happens is that SQLServer keeps evauating the <table_expression_2> and append rows to the final CTE until <table_expression_2> evaluates to an empty set. References to name of the CTE (CTE_name in this example) in table_expression_2 referes to the previous iteration.

EXAMPLE

Consider a table with some sort of hieracy (eg. a folder structure):
Id Parent_id
1 null
2 1
3 1
4 3
Say we want to retrieve the node 4 and all the parent nodes all the way to the root. Using "ordinary"< SQL we can only retrieve a constant number of leves (using the appropriate number of self-joins), but retriving an arbitrary number of levels is a challenge.

An elegant solution using recursive CTE's is:
with CTE_nodes as
(
   select n.id, n.parent_id
   from nodes n
   where n.id=4

   union all

   --- this is the "magic part"
   from CTE_nodes n2              
   inner join nodes n on n.id=n2.parent_id
)
select id, parent_id from CTE_nodes   
The result is:
id parent_id
1null
31
43

The obvious use case is retrieving data from hierarchical tables, but it seems very exciting to have recursive querying capabilities. This has been also been possible using user defined functions, but I have more confidence that the optimzer will be able to handle recursive CTE's more efficiently than recursive user defined functions. I would be happy to hear about real world experience with recursive CTE's.

Certification in progress

It is probably around time I got some certifications taken. In 2009 I took the "Sitecore .NET Developer" certification, but considering how little sitecore work I have done and plans to be doing I could use some more.

SQL Server is a technology I have been working with in depth previously and I should be able to complete the certification without taking too much time off.

At the same time I should get up to date with new enhancements in SQL Server 2012. I have used 2008 a bit but only done serious stuff in SQL Server 2005.

The certification I am aiming for is the "MCSA: SQL Server", so right now I am plowing through this book:

I will post blogs on some of the interesting pieces as I progresses.

lørdag den 27. juli 2013

Upgraded front page

Ok, this was long overdue. I am primarily a back end developer and making pretty layouts on my own is not among my core strengths.

Anyway, my company website tolsit.com was simply based on a standard theme which might be many things but for sure not very impressive.

Thus the front page at least has now been shined up a little bit. This allowed me to refresh a bit of CSS, Gimp and also scratch the surface of Drupal theming.

I am very much aware that the website still does not appear fancy or anything, so hopefully I can improve the rest of the site from time to time.

Keep in mind that I make no claims of being a cool front end developer, so please do not infer much about eg. my database skills from the appearance of the website.

mandag den 15. juli 2013

The Pragmatic Programmer

I actually bought this fine books years ago, but only just now have found time to read it.

Reading it was a pleasure and I can recommend most of the advices it provides. It seems very much in favor of agile techniques (eg. the topics "Tracer Bullets", "The Requirements Pit" and "Refactoring") even though it curiously it does not use the term. Agile methodologies like Scrum were available before 2000 when this book was written, but the "Agile menifesto" appeared in 2001, which might introduced the term?

Many of the tips in the book does not come as surprises at all, but tips like "Your Knowledge Portfolio", "The Power Of Plain Text" and "It's Just a View" are worth keeping in mind.

I am in two minds about the encouragment of code generators. They can be simple but also incredible powerful and it is not for everybody to wrestle with such beasts.

I definitely do not suggest that code generators should be avoided at all times but especially as an independent software developer and consultant I feel a great responsibility to enable the (employees of the) client to maintain and build on whatever I leave behind.