Going Global With SQL Server 2000

1
Going Global With SQL Server 2000

• Beverly Sherry
• Program Manager
• Global Release Services for SQL Server
• Microsoft Corporation

2
(No Transcript)
3
SQL Server Setup

• Clean Install/Collation
• Upgrades/Collation

4
Collation

• Collation what is it and why do we need it to
  support our multilingual data?

5
Collation in SQL Server 6.5 and earlier versions

• No Unicode support
• One code page per server
• One collation per server

6
Collation in SQL Server 7.0

• Unicode datatypes supported
• Two collations
• Unicode
• Non-Unicode
• Number of collations distilled down to the
  minimum necessary
• Collation consistency across OSs

7
Collation in SQL Server 2000

• Combined code pages and Unicode collations into a
  single entity
• Flexible model to specify collations at a more
  granular level

8
"Windows" collations

• 43 language collations
• Added for unique code pages
• Added for unique ordering
• Suffix meanings
• _BIN (Binary)
• _CI/_CS (Case sensitivity)
• _AI/_AS (Accent sensitivity)
• _KS - kanatype sensitivity (hiragana/katakana)
• _WS - width sensitivity (full/half width)

9
SQL Collations

• Provided for backwards compatibility with prior
  versions of SQL Server

10
Collation at four levels

• Server
• Database
• Column
• Expression

11
Collation at the server level

• Acts as a default for all databases
• Can be changed with RebuildM.exe in the
  tools\BINN dir why do you want to do this and
  how.
• Querying the server collation
• SELECT CONVERT(char, SERVERPROPERTY('collation'))

12
Upgrade Path
US
8.0
7.0
French
Korean

• Unicode datatypes in master database
• DB-object metadata converted in Unicode
• Sort order compatibility
• Scripting in Unicode
• Code page override
• .

13
Defining your data store

• UCS-2 Server storage
• Data type
• Unicode
• NCHAR, NVARCHAR, NTEXT,
• META data SYSNAME
• NUnicode
• DBCS/SBCS
• Char, VARCHAR, text

14
Collation at the database level

• Every database has a collation (default is the
  server collation)
• Create database db_test collate
  Latin1_General_CI_AI
• Collation can be changed using
• ALTER DATABASE

SELECT CONVERT(char, DATABASEPROPERTYEX(db_test',
'collation'))
15
Collation at the column level

• Overrides database level collation
• CREATE TABLE jobs
• (
• job_id smallint
• IDENTITY(1,1)
• PRIMARY KEY CLUSTERED,
• job_desc varchar(50)
• COLLATE Arabic_CI_AI_KS
• NOT NULL
• DEFAULT 'New Position - title not
  formalized yet',
• )

16
Collations by Expressions

• SELECT
• FROM
• Table1
• WHERE
• Field1 Field2 COLLATE Turkish_ci_ai

17
The Rules of Precedence for Collations
18
Unicode Data Flow Server

• 8.0 Client
• ODBC 3.7/OLEDB
• TDS 8.0, 8.0 Netlibs support Unicode
• Character data converted to/from Unicode on
  client (Server code page required on client)
• T-SQL batches received in Unicode, parsed in
  Unicode on Server.

• Downlevel client
• DBLIB, ODBC lt3.7 clients
• TDS 4.2
• T-SQL batch received in DBCS/ANSI code page of
  the client
• Translated to Unicode using the Server code page
  on the Server

Application
Application
SQL Server
ODBC
DB-Lib
ODBC
OLE/DB
ODS
Netlib
Netlib
Netlib
TDS 8.0
TDS 4.2
19
Data Flow

• ODBC client to server
• Language event is always in Unicode
• Client ACP to Unicode to server
• A - Unicode to server character set
• NA as Unicode

20
Data Flow

• OLEDB to server
• SSPROP_INIT_AUTOTRANSLATE as VARIANT_TRUE
• Server code page on the client

21
.NET Providers
22
Data Access

• International T-SQL
• NCHAR and N
• No name strings in date/time
• ODBC timestamp
• CONVERT with specific style

23
Data flow

• Data Transformation..

24
Client Flow

• Session language syslanguages
• Precedence
• Set by set language
• Set by connection attribute
• Set by user record in syslogins
• Cultural behavior
• Language of error messages
• Date format, month name
• Day of week and abbreviations

25
BCP

• bcp -w Performs bulk copy operation using
  Unicode characters.
• bcp -N Performs the bulk copy operation using
  the native (database) data types of the data for
  non-character data, and Unicode characters for
  character data.

26
XML

• You can specify an output encoding in a URL.
• XML templates can specify an encoding.
• Unicode by default

27
Full text

• Allows for word or phrase-based indexing of
  character data.
• Full-text indexing enables the creation and
  population of the full-text catalogs, which are
  maintained outside of SQL Server and managed by
  the Microsoft Search service.
• Full-text search uses the new Transact-SQL
  predicates (CONTAINS, CONTAINSTABLE, FREETEXT,
  and FREETEXTTABLE) to query these populated
  full-text catalogs.
• With a full-text query, you can perform
• A linguistic search of character data in tables
  enabled for full-text search.
• A linguistic search operates on words and phrases
  unlike the LIKE predicate which is used to search
  character patterns.
• Manipulate to get what you want

28
Tools Manageability

• Unicode based
• SQL-DMO

29
Tools Web Assistant
30
Backup and Restore

• Restore uses the collation of the source
  databases
• Verify the collation is support on the instance
  of SQL Server

31
Replication
Publisher
Distributor
Updating Subscriber (immediate updates)
Subscriber
Subscriber
32
Analysis Services

• Client Tier
• MD ActiveX Controls
• MD Extension to OLE DB
• Office 2000 Interfaces
• 3rd Party Clients

OLEDB, ADO, XML/A
OLAP Server

• OLAP Server Tier
• Multidimensional data modeling and
• calculation engine
• Persistent multidimensional cache

OLEDB / ODBC
33
Unicode Data Flow in Fringe Areas

• Script usage
• Command line tools
• ISQL utility does not support Unicode input
  files.
• OSQL -u (Specifies that output_file is stored in
  Unicode format).
• Query analyzer, save as Unicode / ANSI / OEM.

34
Resources

• International Features in Microsoft SQL Server
  2000 Http//msdn.microsoft.com/library/default.as
  p
• Arabic Language Support in Microsoft SQL Server
  2000 Http//msdn.microsoft.com/library/default.asp
  
• SQL Server Books On Line