How to: Version SQL Server Modeling Services Schemas

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

This tutorial demonstrates best practices for versioning SQL Server Modeling Services schemas. You add custom data models to the Modeling Services database as a set of tables, views, and other objects that are owned by a SQL Server schema. The Modeling Services design patterns specify guidelines for data model implementation, security, and data access. For more information, see SQL Server Modeling Services Design Patterns. Data models created with the Microsoft code name “M” language follow the Modeling Services design patterns when they are loaded into the Modeling Services database.

Data models evolve over time. Updatable views provide an excellent way to improve the process of versioning schemas. Updatable views should support the most recent versions of the custom schema. The views can provide logic that correctly handles the changes in the underlying base tables. Application developers then have more time to make the necessary changes to use the new schema. Subsequent releases of the schema can remove support for older schema versions.

This tutorial shows how to add new versions of Modeling Services schemas that eliminate the need for users and applications to change immediately.

Creating a Custom Modeling Services Schema

The scenario for this tutorial involves a fictional company named Contoso. Contoso creates an Modeling Services schema to model custom contacts. This schema is named Contoso.Contact. Part of the schema models address entities in a table named AddressesTable. Contoso also creates an updatable view named Addresses that complies with Modeling Services design patterns. Users and applications use the Contoso.Contact schema by accessing the view only. In the first version of the application, the Addresses view and table contain the same members. The following table lists these members.

Column
Id
Folder
Street
City
State
ZipCode

To create the Contact schema

On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.
In the Connect to Server dialog, type the name of the Modeling Services server in the Server name combo box.
Specify the Authentication properties, and then click the Connect button.

Note

Some of the steps in this tutorial require that the user belong to either the RepositoryAdministrator role in the database or the SQL Server sysadmin role.
On the toolbar, click the New Query button. A blank query window should open.

In the blank query window, add the following T-SQL statement. This statement creates a schema named contact that owns an AddressesTable table.

use Repository
go

-- Clean up any previous database objects related to this tutorial.
if (OBJECT_ID('[Contoso.Contact].[Addresses]') is not null)
   drop view [Contoso.Contact].[Addresses]
if (OBJECT_ID('[Contoso.Contact].[AddressesTable]') is not null) 
   drop table [Contoso.Contact].[AddressesTable]
if (SCHEMA_ID('Contoso.Contact') is not null)
   drop schema [Contoso.Contact]
go

-- Create a schema to model contact data.
create schema [Contoso.Contact] authorization [RepositoryOwner]
go

-- Create a table to model Address entities.
create table [Contoso.Contact].[AddressesTable](
   [Id] bigint NOT NULL IDENTITY,
   [Folder] [Repository.Item].[FolderId] not null,
   [Street] nvarchar(100) not null,
   [City] nvarchar(50) not null,
   [State] nvarchar(25) not null,
   [ZipCode] nvarchar(25) not null,
   constraint [PK_Employees] primary key clustered ([Id]))
go

-- Create a Folder for use in this example
if ([Repository.Item].[PathsFolder](N'ContosoAddressesFolder') is null)
insert into [Repository.Item].[Folders]
([Name], [Folder]) values ('ContosoAddressesFolder', null)

Press the F5 key to run the query.

Replace the text in the query window with the following T-SQL statements. These statements create view named [Contoso.Contact].[Addresses]. This view filters rows based on the caller's Folder permissions. For more information about Folders, see SQL Server Modeling Services Folder Design Patterns.

-- Create a view that filters rows based on Folder permissions of the caller.
create view [Contoso.Contact].[Addresses]
(
   [Id],
   [Folder],
   [City],
   [State],
   [Street],
   [ZipCode]
)
as
   select top (9223372036854775807) [AT].[Id], [AT].[Folder], [AT].[City], [AT].[State], [AT].[Street], [AT].[ZipCode]
   from [Contoso.Contact].[AddressesTable] as [AT] with (readcommitted) 
   inner join [Repository.Item].[ReadableFolders]() as [RCV]  on [AT].[Folder] = [RCV].[Folder];
go

grant select on object::[Contoso.Contact].[Addresses] to [RepositoryReader];
go
grant delete, insert on object::[Contoso.Contact].[Addresses] to [RepositoryReaderWriter];
go
grant update on object::[Contoso.Contact].[Addresses]([City], [State], [Street], [ZipCode]) to [RepositoryReaderWriter];
go

Press the F5 key to run the query.
Replace the text in the query window with the following T-SQL statements. The [Repository.Item].[AddViewsInsteadOfTriggers] stored procedure adds INSTEAD OF triggers on the [Contoso.Contact].[Addresses] view. This creates the view updatable and secures access to the view based on Folder permissions. The [Repository.Item].[AddFolderForeignKey] procedure adds a foreign key reference from the Folder column to the [Repository.Item].[FoldersTable] table.
```
exec [Repository.Item].[AddViewsInsteadOfTriggers] @schema = [Contoso.Contact], @view = [Addresses], @idGeneration = 1
exec [Repository.Item].[AddFolderForeignKey] @schema = [Contoso.Contact], @baseName = [Addresses]
```
Press the F5 key to run the query.

To add data to the Addresses view

In the previously opened SQL Server Management Studio query window, replace the text with the following T-SQL statements. These statements add a new row to the AddressesTable table by inserting data into the updatable view, Addresses.

Note

Note that the [Repository.Item].[PathsFolder] function returns the identifier of the system-provided Applications Folder. This tutorial uses the Applications Folder for simplicity, but you normally would create a custom Folder for new data that is specific to your organization or application.
```
use Repository
go

-- Enter some data into the table using the view.
insert [Contoso.Contact].[Addresses]
   (Folder, Street, City, State, ZipCode)
   values ([Repository.Item].[PathsFolder](N'ContosoAddressesFolder'),
            'Main St.', 'Waxhaw', 'NC', '28173')

-- View the current rows in the Addresses view.
select * from [Contoso.Contact].[Addresses]
```
Press the F5 key to run the query. The Results window should show the new address.

Versioning a Modeling Services Schema

In this scenario, Contoso creates several applications that use a variety of data access techniques to access the Addresses view. Contoso first conducts business in the United States only. However, after expanding into international markets, they define a new business requirement to support contacts from multiple countries. International addresses require a Region column. It is also more accurate to change the ZipCode column name to PostCode, because the term Zip Code does not apply to all countries. The following list shows the columns of the modified AddressesTable table.

Column
Id
Folder
Street
City
State
PostCode
Region

Contoso has several applications to update to accommodate this change. It is very difficult to change the underlying data and all dependent applications at one time. Contoso decides to version the schema by changing the Addresses view to contain both the old and new fields to make the view compatible with the previous schema. The view has the following columns.

Column
Id
Folder
Street
City
State
ZipCode
PostCode
Region

Contoso followed Modeling Services design patterns for accessing underlying base tables through updatable views. As a result, all Contoso applications access the Addresses view instead of directly accessing the AddressesTable table. The new view supports the ZipCode field, but the view redirects any changes to this field to the new PostCode field. Now Contoso application developers are able to use normal development and release cycles to change dependent applications to use the new fields in the Addresses view.

To modify the AddressesTable table

In the previously opened SQL Server Management Studio query window, replace the text with the following T-SQL statements. This script adds two new columns to the AddressesTable table named PostCode and Region.
```
-- Modify the table to contain the new columns.
alter table [Contoso.Contact].[AddressesTable] add
   PostCode nvarchar(25) NULL,
   Region nvarchar(100) NULL
go
```
Press the F5 key to run the query.
Replace the text in the query window with the following T-SQL statements. This moves the existing data in the ZipCode field into the PostCode field. It also sets the Region field to a value of United States for each of the current rows. The last statement removes the ZipCode column from the table.
```
-- Move ZipCode data to the new PostCode field.
update [Contoso.Contact].[AddressesTable] set
   PostCode = ZipCode,
   Region = 'United States'
go

-- Remove the ZipCode column.
alter table [Contoso.Contact].[AddressesTable]
   drop column ZipCode
go
```
Press the F5 key to run the query.
Run the following query to view the recent changes to the [Contoso.Contact].[AddressesTable] table.
```
-- View the changes.
select * from [Contoso.Contact].[AddressesTable]
go
```
Press the F5 key to run the query.

To modify the Addresses view

In the SQL Server Management Studio, expand the current SQL Server instance in the Object Explorer window.
In the Object Explorer, navigate to Databases, Repository, and then expand the Views node.
Right-click the Contoso.Contact.Addresses view, point to Script view as, ALTER to, and then click New Query Editor Window.
In the view definition, add [PostCode] and [Region] to the end of the column list.
Then replace the first line of the select statement with the following two lines. This alters the view to show the deprecated ZipCode field. The view shows the value of the PostCode field when ZipCode is queried.
```
   select top (9223372036854775807) [AT].[Id], [AT].[Folder], [AT].[City], [AT].[State], 
      [AT].[Street], [AT].[PostCode] as [ZipCode], [AT].[PostCode], [AT].[Region]
```
Press the F5 key to run the query.
In the Object Explorer, expand the triggers node for the Contoso.Contact.Addresses view.
Right-click the Addresses_InsteadOfInsert_Trigger trigger, and then click Modify. This trigger was created by previous call to the [Repository.Item].[AddViewsInsteadOfTriggers] stored procedure.

Replace the insert statement in the trigger definition with the following statements. These statements use a call to if (update([ZipCode]) to see whether the user is specifying the ZipCode field with this insert. If so, the value passed for ZipCode is placed in the PostCode field and the region is set to United States.

   if (update(ZipCode))
   begin
      insert into [Contoso.Contact].[AddressesTable] ([Folder], [City], [State], [Street], [PostCode], [Region])
      select [Folder], [City], [State], [Street], ISNULL([PostCode],[ZipCode]), ISNULL([Region], N'United States')
      from inserted;
   end
   else
   begin
      insert into [Contoso.Contact].[AddressesTable] ([Folder], [City], [State], [Street], [PostCode], [Region])
      select [Folder], [City], [State], [Street], [PostCode], [Region]
      from inserted;
   end

Press the F5 key to run the query and update the trigger.
In Object Explorer, right-click the Addresses_InsteadOfUpdate_Trigger trigger, and then click Modify.

Replace the update statement in the trigger definition with the following statements.

   if (update([ZipCode]) and not update([PostCode]))
   begin
      update [Contoso.Contact].[AddressesTable]           
      set
         [Folder] = [I].[Folder],
         [City] = [I].[City],
         [State] = [I].[State],
         [Street] = [I].[Street],
         [PostCode] = [I].[ZipCode],
         [Region] = ISNULL([I].[Region], N'United States')
      from inserted as [I]
      where [Contoso.Contact].[AddressesTable].[Id] = [I].[Id]
   end
   else
   begin
      update [Contoso.Contact].[AddressesTable]           
      set
         [Folder] = [I].[Folder],
         [City] = [I].[City],
         [State] = [I].[State],
         [Street] = [I].[Street],
         [PostCode] = [I].[PostCode],
         [Region] = [I].[Region]
      from inserted as [I]
      where [Contoso.Contact].[AddressesTable].[Id] = [I].[Id]
   end

Press the F5 key to run the query and update the trigger.
Replace the text in the query window with the following T-SQL statements. The following two queries demonstrate the flexibility provided by the updatable views. The first insert statement was written for the old schema and used ZipCode. Although this column has been removed from the [Contoso.Contact].[AddressesTable] table, the insert works due to the translation that the updatable view performs to use PostCode and Region instead. The second insert statement successfully uses the new fields.
```
-- Insert rows into the table using the old schema.
insert [Contoso.Contact].[Addresses]
   (Folder, Street, City, State, ZipCode)
   values ([Repository.Item].[PathsFolder](N'ContosoAddressesFolder'),
      'North St.', 'Waxhaw', 'NC', '28173')

-- Insert rows into the table using the new schema.
insert [Contoso.Contact].[Addresses]
   (Folder, Street, City, State, PostCode, Region)
   values ([Repository.Item].[PathsFolder](N'ContosoAddressesFolder'),
      'South St.', 'Waxhaw', 'NC', '28173', 'United States')
go

-- View the current rows in the Addresses view.
select * from [Contoso.Contact].[Addresses]
```
Warning

For this to work seamlessly with applications, you must specify column names for all insertions. This is an accepted best practice for applications that use SQL Server databases. When an application inserts values without specifying column names, the insertions will begin to fail when additional columns are added to the new version of the schema. This happens, because the insert statement assumes that the number and order of columns in the view will remain constant. Always explicitly specify column names to prevent this problem. If this issue is overlooked, an alternative strategy involves modifying the old view to handle the schema change without adding the new columns. Then create a new view with a different name. In the previous example, you can create a new view named Addresses2 that uses the new fields. Updated applications use the new view while older applications continue to use the original view.
Press the F5 key to run the query. You should see both new rows in the Results window.

Deprecating an Modeling Services Schema

In this scenario, Contoso incrementally updates their applications until all of them use the new PostCode and Region columns of the [Contoso.Contact].[Addresses] view. Contoso then removes the ZipCode field and its associated logic from the view and triggers.

To modify the Addresses view

In the previously opened SQL Server Management Studio query window, replace the text with the following T-SQL statements. This script drops the existing [Contoso.Contact].[Addresses] view. It recreates the view without the deprecated ZipCode field. It then makes a call to the [Repository.Item].[AddViewsInsteadOfTriggers] stored procedure to regenerate standard INSTEAD OF triggers on the current version of the [Contoso.Contact].[Addresses] view.

use Repository
go

if exists (select * from sys.views where object_id = object_id(N'[Contoso.Contact].[Addresses]'))
drop view [Contoso.Contact].[Addresses]
go

-- Create a view that filters rows based on Folder permissions of the caller.
create view [Contoso.Contact].[Addresses]
(
   [Id],
   [Folder],
   [City],
   [State],
   [Street],
   [PostCode],
   [Region]
)
as
   select top (9223372036854775807) [AT].[Id], [AT].[Folder], [AT].[City], [AT].[State], 
      [AT].[Street], [AT].[PostCode], [AT].[Region]
   from [Contoso.Contact].[AddressesTable] as [AT] with (readcommitted) 
   inner join [Repository.Item].[ReadableFolders]() as [RCV]  on [AT].[Folder] = [RCV].[Folder];
go

grant select on object::[Contoso.Contact].[Addresses] to [RepositoryReader];
go
grant delete, insert on object::[Contoso.Contact].[Addresses] to [RepositoryReaderWriter];
go
grant update on object::[Contoso.Contact].[Addresses]([City], [State], [Street], [PostCode], [Region]) to [RepositoryReaderWriter];
go

exec [Repository.Item].[AddViewsInsteadOfTriggers] @schema = [Contoso.Contact], @view = [Addresses], @idGeneration = 1
exec [Repository.Item].[AddFolderForeignKey] @schema = [Contoso.Contact], @baseName = [Addresses]

Press the F5 key to run the query.

Share via

How to: Version SQL Server Modeling Services Schemas

Creating a Custom Modeling Services Schema

To create the Contact schema

To add data to the Addresses view

Versioning a Modeling Services Schema

To modify the AddressesTable table

To modify the Addresses view

Deprecating an Modeling Services Schema

To modify the Addresses view

See Also

Concepts

Additional resources