1 / 34

Going Global With SQL Server 2000

Going Global With SQL Server 2000. Beverly Sherry Program Manager Global Release Services for SQL Server Microsoft Corporation. SQL Server Setup. Clean Install/Collation Upgrades/Collation. Collation. Collation what is it and why do we need it to support our multilingual data?.

melissa-rosales
Download Presentation

Going Global With SQL Server 2000

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Going Global With SQL Server 2000 Beverly Sherry Program Manager Global Release Services for SQL Server Microsoft Corporation 22nd International Unicode Conference San Jose, CA September 2002

  2. 22nd International Unicode Conference San Jose, CA September 2002

  3. SQL Server Setup • Clean Install/Collation • Upgrades/Collation 22nd International Unicode Conference San Jose, CA September 2002

  4. Collation • Collation what is it and why do we need it to support our multilingual data? 22nd International Unicode Conference San Jose, CA September 2002

  5. Collation in SQL Server 6.5 and earlier versions • No Unicode support • One code page per server • One collation per server 22nd International Unicode Conference San Jose, CA September 2002

  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 OS’s 22nd International Unicode Conference San Jose, CA September 2002

  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 22nd International Unicode Conference San Jose, CA September 2002

  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) 22nd International Unicode Conference San Jose, CA September 2002

  9. SQL Collations • Provided for backwards compatibility with prior versions of SQL Server 22nd International Unicode Conference San Jose, CA September 2002

  10. Collation at four levels • Server • Database • Column • Expression 22nd International Unicode Conference San Jose, CA September 2002

  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')) 22nd International Unicode Conference San Jose, CA September 2002

  12. Upgrade Path US • Unicode datatypes in master database • DB-object metadata converted in Unicode • Sort order compatibility • Scripting in Unicode • Code page override . 8.0 ７.０ French Korean 22nd International Unicode Conference San Jose, CA September 2002

  13. Defining your data store • UCS-2 Server storage • Data type • Unicode • NCHAR, NVARCHAR, NTEXT, • META data – SYSNAME • N’Unicode’ • DBCS/SBCS • Char, VARCHAR, text 22nd International Unicode Conference San Jose, CA September 2002

  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')) 22nd International Unicode Conference San Jose, CA September 2002

  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', ) 22nd International Unicode Conference San Jose, CA September 2002

  16. Collations by Expressions SELECT * FROM Table1 WHERE Field1 = Field2 COLLATE Turkish_ci_ai 22nd International Unicode Conference San Jose, CA September 2002

  17. The Rules of Precedence for Collations 22nd International Unicode Conference San Jose, CA September 2002

  18. Unicode Data FlowServer • Downlevel client • DBLIB, ODBC <3.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 • 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. Application Application SQL Server ODBC DB-Lib ODBC OLE/DB ODS Netlib Netlib Netlib TDS 8.0 TDS 4.2 22nd International Unicode Conference San Jose, CA September 2002

  19. Server Client Client side conversion char Svr CHAR ACP SQL_C_CHAR Unicode CHAR bytes NO TRANSLATION SQL_C_CHAR CHAR Unicode NCHARSYSNAME Unicode ACP SQL_C_CHAR char CHAR Svr CHAR SQL_C_WCHAR Unicode Data Flow ODBC client to server • Language event is always in Unicode • Client ACP to Unicode to server • ‘A’ - Unicode to server character set • N‘A’ as Unicode 22nd International Unicode Conference San Jose, CA September 2002

  20. Server Client Client side conversion char Svr CHAR ACP DBTYPE_STR Unicode CHAR bytes VARIANT_FALSE DBTYPE_STR CHAR Unicode NCHARSYSNAME Unicode ACP DBTYPE_STR char CHAR DBTYPE_WSTR Svr CHAR Unicode Data Flow • OLEDB to server: • SSPROP_INIT_AUTOTRANSLATE as VARIANT_TRUE • Server code page on the client 22nd International Unicode Conference San Jose, CA September 2002

  21. .NET Providers 22nd International Unicode Conference San Jose, CA September 2002

  22. Data Access • International T-SQL • NCHAR and N’’ • No name strings in date/time • ODBC timestamp • CONVERT with specific style 22nd International Unicode Conference San Jose, CA September 2002

  23. Steps Source Destination OLE DB ODBC Fixed field ASCII delimited OLE DB ODBC Fixed field ASCII delimited HTML page Repl. publication Xforms DTS Data Pump In Out Data flow • Data Transformation….. 22nd International Unicode Conference San Jose, CA September 2002

  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 22nd International Unicode Conference San Jose, CA September 2002

  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. 22nd International Unicode Conference San Jose, CA September 2002

  26. XML • You can specify an output encoding in a URL. • XML templates can specify an encoding. • Unicode by default 22nd International Unicode Conference San Jose, CA September 2002

  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 22nd International Unicode Conference San Jose, CA September 2002

  28. Tools Manageability • Unicode based • SQL-DMO 22nd International Unicode Conference San Jose, CA September 2002

  29. ToolsWeb Assistant 22nd International Unicode Conference San Jose, CA September 2002

  30. Backup and Restore • Restore uses the collation of the source databases • Verify the collation is support on the instance of SQL Server 22nd International Unicode Conference San Jose, CA September 2002

  31. 2PC, RPC Replication Publisher Distributor Updating Subscriber (immediate updates) Subscriber Subscriber 22nd International Unicode Conference San Jose, CA September 2002

  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 22nd International Unicode Conference San Jose, CA September 2002

  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. 22nd International Unicode Conference San Jose, CA September 2002

  34. Resources • International Features in Microsoft SQL Server 2000 Http://msdn.microsoft.com/library/default.asp • Arabic Language Support in Microsoft SQL Server 2000Http://msdn.microsoft.com/library/default.asp • SQL Server Books On Line 22nd International Unicode Conference San Jose, CA September 2002

More Related