Choosing an Identity Pattern (Modeling Services)
[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.]
SQL Server Modeling Services defines several types that you can use to add an identity field to a model that is written in Microsoft code name “M”. When writing “M” models for Modeling Services, you should try to use one of the types described in this topic to define an identity for your types and extents. This reduces the amount of code you have to write and ensures the consistent implementation of these common patterns.
To use these identifier patterns, you must reference the Repository.dll assembly and import the System module. For more information, see SQL Server Modeling Services Patterns.
Note that these identity patterns are based on “M” identity concepts. For more information, see Choosing an Identity Field for each Extent.
Note
Note that there are also types that define both the identifier pattern and the Folder pattern. These are not listed in the following sections. For more information, see Adding Folders (Modeling Services).
HasId
The HasId type contains an Integer64 field named Id. The example below shows a Contact schema with a PhoneNumbers extent. Instead of defining the Id column directly, the extent imports the System module and uses the HasId type.
module Contact
{
import System;
PhoneNumbers :
{(
HasId &
{
Work2 : Text;
Home : Text?;
Mobile : Text?;
}
)*};
}
Note that with HasId, you must specify the Id value when inserting any new records into the extent. The identifier is not automatically assigned. Use HasId when you want full control over the numeric identifier for an extent.
HasAutoId
The HasAutoId type also contains an Integer64 field named Id. The distinguishing characteristic for this type is that the Id field is automatically assigned a value. This is done by setting the Id field to a default value of AutoNumber. The following example shows the PhoneNumbers extent using the HasAutoId type.
module Contact
{
import System;
PhoneNumbers :
{(
HasAutoId &
{
Work2 : Text;
Home : Text?;
Mobile : Text?;
}
)*};
}
Note that with the HasAutoId type, you cannot explicitly assign the Id field when storing new records in the extent. Instead, assign values to the other fields for the new record, and the Id field is implicitly assigned the next unique Integer64 value.
HasSequenceId
Modeling Services supports sequence objects. Sequence objects allow Modeling Services to provide unique identifiers for new extent records. The benefit of sequence objects is in bulk insert operations. When using HasAutoId, there is a small performance penalty for the database to automatically assign the identifier. With sequence objects, you can call a Modeling Services stored procedure, [Repository].[NewIdInterval], to reserve a range of identifiers. Requesting the range of identifiers once improves the performance of large insertions. It also shares the benefits of the HasAutoId, because Modeling Services generates the identifiers and ensures their uniqueness. If you also use updatable security views, you can add rows without explicitly providing an identifier, and the view will automatically assign the next sequence identifier. For more information, see Creating Security Views (Modeling Services).
The HasSequenceId type contains an Integer64 identifier named Id. The default value for this field is set to NewIdInterval. This specifies that sequence objects are used to assign the identifiers for this extent. The following shows the use of HasSequenceId with the PhoneNumbers extent.
module Contact
{
import System;
PhoneNumbers :
{(
HasSequenceId &
{
Work2 : Text;
Home : Text?;
Mobile : Text?;
}
)*};
}
With the HasSequenceId type, you cannot explicitly assign the Id field when storing new records in the extent by using “M”. Instead, assign values to the other fields for the new record, and the Id field is implicitly assigned the next unique Integer64 value. It obtains this unique value from the sequence object for the extent.
It is important to note that there are different requirements if you manually add new records in the database as opposed to defining the new records in “M”. If you load this Contact module into a target Modeling Services database, a new sequence object is created for you. A sequence object name consists of two parts: a schema name and an object name. In this example, the sequence object in the database would have a schema name that matches the module name, Contact, and it would have an object name that matches the extent name, PhoneNumbers. To manually add rows to the PhoneNumbers extent in the database, you must acquire the identifiers first by calling the [Repository].[NewIdInterval] stored procedure. This procedure accepts parameters for the sequence object's schema name and object name as well as the number of required identifiers. Then you explicitly assign the returned identifiers to the new rows that you insert. This differs from adding the new rows by using “M”, because with “M” you do not explicitly specify the identifiers; this is done for you by the “M” compiler.
Warning
When adding new rows to an extent that uses sequence objects, only use identifiers returned from the [Repository].[NewIdInterval] stored procedure. Adding identifiers in any other way can break the pattern and cause future insertions to fail.
If you use updatable security views, you do not need to explicitly call [Repository].[NewIdInterval]. Instead, you can insert new rows into the views without providing identifiers. The triggers on the view call the [Repository].[NewIdInterval] procedure for you to implicitly generate the correct identifiers.
For more information about sequence objects in the Modeling Services database, see Identifier Design Patterns.
Note
The HasSequenceId automatically creates the sequence object for you in the Modeling Services database. However, it is also possible to create the sequence object by using the PatternApplication sample to assign the Patterns.CreateIdSequence pattern to the target extent. Using the PatternApplication sample to create the sequence object is typically not necessary unless you want to use sequence objects with an Integer64 identifier that does not have its default set to NewIdInterval. For more information about the PatternApplication sample, see Using the PatternApplication Sample.
See Also
Concepts
SQL Server Modeling Services Patterns