{} Computed Values ("M" Reference)

Article
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

Computed values are expressions that are executed at runtime and produce a value in memory, unlike extents, which exist in storage. This section describes the syntax of computed values, and provides simple examples.

Syntax

Computed value declarations consist of an identifier, 0 or more input parameters, an optional return type, and an expression surrounded by braces. A computed value looks like an extent declaration, except that instead of a terminating “;”, there is a pair of braces.

The identifier is any valid Microsoft code name “M” identifier.

The set of parameters is enclosed in a single pair of parentheses, and separated by commas. Each parameter consists of an identifier and optionally an ascribed type.

The return type is specified as an ascribed type.

The expression inside the braces can be any valid “M” expression, including query statements. Computed values that specify 0 parameters generate a view in SQL. Those with one or more generate a SQL function.

Unlike extent declarations, a computed value declaration is not terminated by a semi-colon.

Computed value declarations can occur inside a type declaration, as well as at the module level.

Examples

Return Type Specified

In this example, the computed value SeattleAddresses has a return type of Address*.

module CV
{
    type Address 
    { 
        Id : Integer32 = AutoNumber();
        Street : Text;
        Zip : Integer32;
    } where identity Id;
    Addresses : Address*;
// a computed value
    SeattleAddresses() : Address* {Addresses where value.Zip > 98100 && value.Zip < 98200}
}

Return Type Omitted

When no type is specified, “M” ascribes a type automatically, based on the declared result type of the underlying expression.

In the following example, because the logical and operator is used, the expression returns a Logical expression, and the IsSeattle() computed value is inferred to have a Logical return type.

module CV
{
    type Address 
    { 
        Id : Integer32 = AutoNumber();
        Street : Text;
        Zip : Integer32;
    } where identity Id;
    Addresses : Address*;
// a computed value
    SeattleAddresses() {Addresses where value.Zip > 98100 && value.Zip < 98200}
}

Parameters in Computed Values

A computed value can optionally declare a list of named parameters whose values must be specified when using the computed value in an expression. When parameters are specified, a SQL function is generated instead of a view.

Here is an example of a computed value that has a parameter. The MatchingZips computed value returns the Addresses that match the TargetZip input parameter.

module CV
{
    type Address 
    { 
        Id : Integer32 = AutoNumber();
          Street : Text;
          Zip : Integer32;
    } where identity Id;
    Addresses : Address*;
    MatchingZips(TargetZip : Integer32) 
   { 
      from a in Addresses 
      where a.Zip == TargetZip 
      select a
   }
}

This “M” code generates the following T-SQL code.

create table [CV].[Addresses]
(
  [Id] int not null identity,
  [Street] nvarchar(max) not null,
  [Zip] int not null,
  constraint [PK_Addresses] primary key clustered ([Id])
);
go

create function [CV].[MatchingZips]
(
  @TargetZip as int
)
returns table
  as return (
    select [a].[Id] as [Id], [a].[Street] as [Street], [a].[Zip] as [Zip]
    from [CV].[Addresses] as [a]
    where [a].[Zip] = @TargetZip
  )
go

To use this computed value in an expression, you must provide values for the parameter. The following computed value is an example.

MatchingAddresses() {MatchingZips(98144)}

This code generates the following SQL view.

create view [CV].[MatchingAddresses]
(
  [Id],
  [Street],
  [Zip]
)
as
  select [Id] as [Id], [Street] as [Street], [Zip] as [Zip]
  from [CV].[MatchingZips]( 98144);

Share via