I just installed the Allocation Information add-in for SQL Server Management Studio.  It's very cool having this tool integrated into SSMS. It is a free add-in published on CodePlex. Take a look, it does worth the download in my opinion.

I've just read this interesting article from Anith Sen that lists many different ways to concatenate several row values into a single column (i.e. one row for each category with a field containing a comma separated list of products for that category).

I admit I never thought to use the FOR XML PATH solution to get this kind of result. Not only this is a very elegant way to write the query (you formally are not using other recursive approaches based on CTE), but it seems to be also the better solution from a performance point of view. I didn't compared it with the CLR based one, but the performance seems to be so good with the FOR XML PATH solution that I think I will use that by default unless performances are very very critical - I would consider the CLR based solution only if I already have a CLR assembly deployed, but I wouldn't deploy an assembly just for this feature.

A few months ago I talked about SQL Server Virtualization. For me, taking a look at virtualization improvements is very important, because I still think that not every database can be virtualized, but many of them probably yes! And the trend is that the number of databases that can be "virtualized" is growing for each new version of the virtualization environments. Hyper-V is an important milestone for Microsoft in that direction.

That said, I know that a lot of people (like me) have to make a decision about virtualization today, but they also want to get an overview of emerging trends and products in this market. A good news for us is that an international independent congress will be held in London on 15-16 October, 2008: the Virtualization Congress 2008.

Looking at the agenda, I discovered that Mark Russinovich will be the speaker from Microsoft and he also registered a promo video for the conference. Other speakers are from the most important players in this market. This conference is very different from the technical conference I'm used to attend from Microsoft (in fact, it is not from Microsoft!). However, the presence of people like Mark grants me that it shouldn't be a set of marketing sessions.

Another good excuse to visit London (if you, like me, are not from UK) after SQLBits.

The last news is the acquisition of DATAllegro by Microsoft. From a server point of view, Microsoft is doing well (even if not fast as I'd like). But is on the client side that I (and many other customers) are disappointed.

I totally agree with the post of Chris Webb about XLCubed, where he also explains the issues of Microsoft client tools that are on the market today. I know, we have to wait until Office 14 and/or PerformancePoint 2.0 before having a more advanced BI tool. We only have to think what to say to customers that would like to make strategical investment today and don't want to wait other 2 or more years just to get a client tool that is able to leverage on features that (for example) Analysis Services offered since 2005.

The reason for not investing too much on a third party tool is simple. Until three years ago, Panorama and ProClarity were the most important OLAP tools you could use with Analysis Services. Then, Microsoft acquisition of ProClarity stopped most of its development (because of the need to integrate it with PerformancePoint) and Panorama failed to update its client to leverage on new SSAS 2005 attribute-based paradigm. If only someone predicted the inability of Microsoft to deliver a full-featured client OLAP product, updated with fancy graphics, within 2006/2007, a very good move in 2004 would have been developing a ProClarity equivalent product made using .NET and WPF as development platform. Today, it would have been a killer-app, especially if you consider that Silverlight 2 allows you to deliver the fancy animated charts in a simple way. Today, I don't see any "wow" OLAP client on the market that I can plug to SSAS using all the SSAS features and power.

As Chris said, binding the release cycle of OLAP tools to the release cycle of Office hasn't been a good move. The sessions announced for the Microsoft Business Intelligence Conference 2008 don't anticipate much for the near future. Yes, we have to wait for Office 14...

I would like to attend to SQLBits this year, but my agenda is already overbooked. I hope you have the time to attend because there are a lot of interesting sessions, it's a good excuse to visit London (the conference is scheduled on September 13, 2008 - it's a Saturday) and there are very good speakers. The conference is free, but registration is required. A training day (with a reasonable fee) is also scheduled the day before. If you live in Europe, save the date!

Thanks to a comment sent by a reader of my M2M paper, I just found a possible bug (or "not supported by design feature") of the CREATE LOCAL CUBE mdx statement. In short, it seems that a simple many-to-many dimension relationship is supported, but if you have a cube with multiple many-to-many dimension relationships chained together, this statement does not work. More details are described in the bug I opened on Connect site: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354521

I know that CREATE LOCAL CUBE is not the best way to create local cubes. This paper explains why. However, this is the statement that Excel 2007 uses to create Offile cubes. So, this bug compromise the ability to put a cube offline with Excel 2007 if you have cascading many-to-many relationships chained together.

A comment received by one reader of Programming LINQ suggested me to underline a concept that is not so intuitive using LINQ, especially if you come from years of SQL coding.

The idea is very simple. Two entities in LINQ might be related in the model. Whenever this happen, usually it is better to leverage on this existing relationship and not to write the join syntax in an explicit way. If you are using LINQ to SQL, the generated SQL code might be more performant or at least correspondant to the one generated by writing an explicit join in your LINQ query. The less constraints in your query, the better.

Let's look at an example on the Northwind database. Imagine you want to see a list of all categories with a flag set for the one which a particular product belongs to. This is a SQL query we could write:

SELECT
    c.CategoryID, 
    c.CategoryName,
    CASE WHEN p.ProductID IS NULL 
        THEN 0
        ELSE 1
    END AS Selected
FROM Categories c
LEFT JOIN Products p
    ON p.CategoryID = c.CategoryID
    AND p.ProductID = 10
ORDER BY CategoryName

Ok, we can write the same query in many other ways, but there are several more complex situations where a LEFT JOIN is used to test the presence of an element in a related table. A correspondant LINQ query might be the following one:

from c in dc.Categories
orderby c.CategoryName
join p in dc.Products.Where(p => p.ProductID == 10)
    on c.CategoryID equals p.CategoryID 
    into pj
from x in pj.DefaultIfEmpty()
select new {
    c.CategoryID,
    c.CategoryName,
    Selected = x != null
};

The LINQ query above will generate a SQL query containing a LEFT JOIN statement. However, a relationship exists between Categories and Customer, and you can leverage on this relationship in the point where you really need to traverse the relationship (in the projection statement). The following one is a better way to get the same result:

from c in dc.Categories
orderby c.CategoryName
select new {
    c.CategoryID, 
    c.CategoryName,
    Selected = c.Products.Any( p => p.ProductID == 10 ) ? true : false
};

This new version has two advantages. First, it is shorter and express its intent more explicitly.  Second, it generates a SQL query with an EXISTS statement, similar to the following one.

SELECT CategoryID, CategoryName,
    (CASE
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM Products AS p
            WHERE (p.ProductID = 10) AND (p.CategoryID = c.CategoryID)
            ) THEN 1
        ELSE 0
    END) AS Selected
FROM Categories AS c
ORDER BY CategoryName

The execution plan used by SQL Server might be similar if not equal. However, using the implicit relationship between Categories and Products in the LINQ query is usually better, because it gives more freedom to the LINQ provider to generate a more efficient SQL code.

I and Paolo Pialorsi have been interviewed at TechEd by Ken Rosen. We talked about our experience as book authors of Programming LINQ.

If you are interested in writing a book, or if you simply want to see our faces and hear our italian accent, you can watch the video available in both low resolution and high resolution. Enjoy!