Localization Design Pattern

[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 topic explains the guidelines for localizing string and resource data within the SQL Server Modeling Services. The System.Globalization schema contains tables, views, stored procedures, and functions for supporting localization. Localized data within Modeling Services tables is useful for tools and applications that want to present a customized experience to users from different locales.

Tip

This topic explains how to use the Modeling Services pattern for localization directly in SQL Server. If you are creating models with Microsoft code name “M”, refer to the topic on using this Modeling Services pattern in “M”. For more information, see Adding Localized Strings and Resources (Modeling Services).

Guidelines for Localization

The following list provides the guidelines for storing localized strings and resources in Modeling Services tables:

1. Add Localized Strings to the [System.Globalization].[Strings] View.

2. Add Localized Resources to the [System.Globalization].[Resources] View.

3. Add Columns to Reference Localized Strings and Resources.

Note

Database administrative privileges are required for performing many of these manual design tasks in the Modeling Services database.

Add Localized Strings to the [System.Globalization].[Strings] View

The [System.Globalization].[Strings] view stores multiple localized versions of the same string. Each string is identified by its Id column of type nvarchar(2083). Multiple localized versions of the same string all share the same Id value. Unique strings are determined by a combination of the string identifier, the locale, and the Modeling Services Folder identifier. The possible locale values are stored in the [System.Globalization].[Locales] view.

Example

The following T-SQL example adds two localized versions of the same string. To ensure the uniqueness of the string identifier, the identifier is set to a string of a GUID. Then this identifier is used for two inserts into the [System.Globalization].[Strings] view to localize the word “Hello” for English, en-US, and French, fr-FR.

use Repository
go

declare @string_id as nvarchar(2083)
set @string_id = 'DDF3C425-7229-4D6F-9039-2B5766531598'

insert [System.Globalization].[Strings] ([Id], [Folder], [Locale], [String])
   select @string_id, 1, N'en-US', N'Hello'
   union
   select @string_id, 1, N'fr-FR', N'Bonjour'
go

Note

Note that these examples use hard-coded GUID strings. Applications will normally generate these GUIDs automatically or have previously generated the GUIDs for consistent use in the application code. To generate unique GUID strings that differ from these examples, you can use a tool, such as the Create GUID tool that ships with Visual Studio. Alternatively, you can perform a simple T-SQL statement, select NEWID().

Add Localized Resources to the [System.Globalization].[Resources] View

Applications also require the localization of resources, such as icons or bitmaps. The design for storing localized resources is fundamentally the same as the design for storing localized strings. The [System.Globalization].[Resources] view stores localized resources identified by a an nvarchar(2083) Id column. Resource formats vary, but all of them can be stored as binary data. The [System.Globalization].[Resources] view contains a varbinary(max) column named Resource to store the binary representation of the resource.

Example

The following T-SQL example adds two localized bitmap resources to the [System.Globalization].[Resources] view. This assumes that there are English and French copies of the bitmap located at c:\resources\en\bitmap.bmp and c:\resources\fr\bitmap.bmp respectively. This example uses the OPENROWSET SQL Server function to bulk load the files into the binary Resource column.

use Repository
go

declare @resource_id as nvarchar(2083)
set @resource_id = '422AE129-48BE-4A32-809C-057C89F1C224'

insert into [System.Globalization].[Resources]([Id], [Locale], [Resource], [Folder]) 
   select @resource_id, N'en-US', BulkColumn, 1
   from OPENROWSET(bulk N'c:\resources\en\bitmap.bmp', single_blob) as document
   union
   select @resource_id, N'fr-FR', BulkColumn, 1
   from OPENROWSET(bulk N'c:\resources\fr\bitmap.bmp', single_blob) as document
go

Add Columns to Reference Localized Strings and Resources

To use localized strings and resources, add nvarchar(2083) columns to target tables that require localized strings or resources. Note that you cannot create FOREIGN KEY constraints to the corresponding tables in the System.Globalization schema, because there are potentially multiple rows in the localization tables for the same string or resource identifier.

Example

The following T-SQL example creates a schema named Location that owns a table named CountriesTable. In this scenario, applications that access this table require translations of the country names to display to users with different locale settings. After creating the table, the script adds the localized strings for United States to the [System.Globalization].[Strings] view for both English, en-US, and French, fr-FR. The string identifier is then used for an insert into the CountriesTable table.

use Repository
go

-- Create a Location schema with a CountriesTable that
-- references a localized string for CountryName.
create schema [Location] authorization [RepositoryOwner]
go
create table [Location].[CountriesTable](
   [CountryName] uniqueidentifier NOT NULL,
) on [PRIMARY]
go

-- Localize the name of the country "United States" and
-- insert a row into the [Location].[CountriesTable] that
-- references this string identifier.
declare @string_id as nvarchar(2083)
set @string_id = '88410D0D-687C-4280-B1BB-A1A46E144392'

insert [System.Globalization].[Strings] ([Id], [Folder], [Locale], [String])
   select @string_id, 1, N'en-US', N'United States'
   union
   select @string_id, 1, N'fr-FR', N'Etats-Unis'

insert [Location].[CountriesTable] ([CountryName])
values (@string_id)

-- Display the affected rows.
select * from [Location].[CountriesTable]
select * from [System.Globalization].[Strings] where Id = @string_id
go

See Also

Concepts

SQL Server Modeling Services Design Patterns
SQL Server Modeling Services Architecture