|Static Named Sets v/s Dynamic Named Sets|
|Written by Jason Thomas|
|Tuesday, 25 May 2010 21:53|
Reposted from Jason Thomas blog with the author's permission.
It is no big secret that most of the questions that are asked in interviews today are repeated. One of those questions which interviewers just love to ask again and again is the difference between a static named set and a dynamic named set. The popularity of this question is further aided by the fact that this particular feature of dynamic named sets was newly introduced in SQL Server 2008. So here I continue my interview question series on this topic.
Many times, it can happen that we end up writing very complex MDX expressions revolving sets. To simplify such expressions or to improve the performance of some queries, we can extract the definitions of some sets into a separate named set expression which are called named sets. A named set can be created either using a CREATE SET statement or a WITH SET statement (For syntax, refer Building Named Sets in MDX). In SQL Server 2005, there was only the Static named sets feature but in SQL Server 2008, both Dynamic and Static named sets are present.
Static Named Sets
The value of a static named set is evaluated either when the CREATE SET statement is executed (if it is defined inside the cube) or right after the WHERE clause is resolved (if the set is defined within a WITH clause). Hence, when a query references a static named set, the set is not resolved again in the context of the current coordinates which explains why it is known as a static named set.
To explain this better, let us consider a scenario where we want to count the number of years for a particular country where the order is greater than 4000.
This will give us the following output
Now let us simulate creating a static named set in the Adventure Works cube and see the result by running the following query in SQL Server Management Studio (SSMS).
Note that we are using GO statement in between, else you will get an error in SSMS saying that multiple statements are not allowed. As per the explanation above, the set would be evaluated when the CREATE SET statement is executed and the coordinates at that context for Customer Geography is ALL. So now when the set is referenced in the query below, it will the count for the ALL member even if Canada is selected.
To verify this, I queried the cube to just return the count of countries when no country is selected and the country count matches with the above result.
Now what do we do if I just wanted to consider the coordinates in the where condition and return the result based on it (in this eg, consider the filter condition on Canada and return just the periods having order greater than 4000 for Canada which is 1)? That is where Dynamic sets come to our aid.
Dynamic Named Sets
With the help of dynamic named sets, we can create a named set which would be revaluated in context of each query that references it. It is evaluated in the context of WHERE clause and SubSelect of every query but are not evaluated in the context of every cell. The most visible difference would be in the case of dynamic named sets created in the cube. Let us use the previous example and see what happens when we use a dynamic set instead of a static set.
The output is shown below
Finally we get the result we want taking the filter conditions in the WHERE clause (for Canada). We can also test what happens when we remove Canada from the WHERE clause and introduce it on the rows.
As mentioned, the set will be evaluated using the current coordinates which would be ALL for Country as there is nothing on the WHERE clause. The output is shown below
Now that you have understood that the difference between a static named set and dynamic named set is not only the difference in their syntax, you might want to go to the following blogs by Mosha (who is called the father of MDX) and understand how a named set improves performance