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.
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.
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.