Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Unicode and Collation in SQL

Collation at four levels:
  1. Server
  2. Database. Every database has a collation (default is the server collation). the particular collation needs to be present on the operating system as well.
  3. Column. Overrides database level collation.
  4. Expression. Can be used to override any other collation, uses the COLLATE keyword.

--Querying the server collation:
SELECT CONVERT(char, SERVERPROPERTY('collation'))
--Querying the DB collation:
SELECT DATABASEPROPERTYEX(db_name(), 'Collation') SQLCollation
--Find Collation of a Table Column:
SELECT name, collation_name FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'History')
AND name = 'AssignmentName'

Non-Unicode to Unicode Data conversion:
  • Use unicode (n*) data type (nvarchar, nchar,  ntext) for multi-language support.
  • Defining Parameters in Stored Procedures with a Unicode data type.
  • Using the N Prefix - @name = N'Chain'.

Points to remember before Non-Unicode to Unicode Data conversion:
  • The storage size of the columns grows to twice size.
  • indexes, constraints and triggers on the columns have to be removed and recreated.
  • If your tables are large it takes its time.

  • Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales
  • Collation settings doesn't affect the data stored in columns of unicode data type, so - as long as you remember about N' prefix - collation is important only according to sort order.
  • the collation impacts data comparision tests.
  • In SQL Server 2000, you use the ntext data type instead of nvarchar(max).
  • The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar. In those cases, replace to nchar(max), nvarchar(max).
  • The Unicode types (distinguished by the "n" in front of the type name) uses twice as many bits (two, in fact) to store the data. So - it also need to be concerned about how much more storage you'll need with Unicode data
More useful selects:
-- Get a list of all the collations supported by SQL Server:
SELECT Name, Description FROM fn_helpcollations()

-- get not unicode columns
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ,COLUMN_NAME, DATA_TYPE ,ORDINAL_POSITION, COLUMN_DEFAULT , IS_NULLABLE
from information_schema.columns
where DATA_TYPE in ('varchar', 'char', 'text')
order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME


Collation Types

Get a property of a specified collation:
COLLATIONPROPERTY(collation_name,property ):
SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage') --result: 1252 


Links:
Articles:
Unicode and SQL Server, good introduction to Unicode and collation
Implementation of Unicode in SQL Server


MSDN:
International Considerations for Databases and Database Engine Applications:
Server-Side Programming with Unicode:
SQL Server Collation Names:

Forums:
Any thoughts on collation settings for an International database?

No comments:

Post a Comment