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:
Add Localized Strings to the [System.Globalization].[Strings] View.
Add Localized Resources to the [System.Globalization].[Resources] View.
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