Hi, I am using SQL 2008 R2
CREATE TABLE dbo.CollationCheck(PKColumn int NOT NULL IDENTITY (1, 1), ID int identity(1,1), NVarcharColumn nvarchar(50) NULL, )
db has SQL_Latin1_General_CP1_CI_AS collation
insert into CollationCheck (NVarcharColumn) values (N'えましょう')
insert into CollationCheck (NVarcharColumn) values ('えましょう')
it did nt work. throws ????? as result
How can we do database level changes like collation changes to work statement 2 without
prefix N'value ?
Note: I did nt want to do code level changes while write Statement. I want it on database level
Answer by srutzky ·
insert into CollationCheck (NVarcharColumn) values ('えましょう') > > it didn't work. shows ????? as result
Yes, that is the expected behavior given the Database's current default Collation of SQL_Latin1_General_CP1_CI_AS.
How can we do database level changes like collation changes to work statement 2 without prefix N'value ?
You can't. If you have a string literal without the `N` prefix, then that string is `VARCHAR`. It doesn't matter what characters you put into that string, the string can only hold an 8-bit character set.
Just try a simple `SELECT` of that same string:
Note: I didn't want to do code level changes while write Statement. I want it on database level
If you are creating the SQL dynamically in the app code, then you have no choice but to prefix the strings with `N`.
Sorry to have previously given less-than-fully-accurate info before, but there was something that I was unaware of at the time. While I was correct that string literals that do not have the upper-case "N" prefix are 8-bit encoded / VARCHAR, I was incorrect that there is no possible way to allow for these characters in a VARCHAR column / literal / variable / parameter.
There are, in fact, a few 8-bit encodings that do support these characters. These encodings are double-byte character sets (DBCS) and are variable-size encodings. For standard ASCII characters they use a single byte. But for going beyond the typical 256 character limit of most 8-bit encodings, they use two bytes in combination for the rest of the characters that they support. There are 4 such encodings supported by Windows:
Prior to the release of SQL Server 2000, the only Collations available were what are now the SQL Server Collations (those with names starting with "SQL_"). None of the SQL Server Collations support any of the 4 DBCS encodings / code pages listed above. But the Windows Collations (those with names not starting with "SQL_", which were introduced in SQL Server 2000, do support all 4 of those DBCS code pages. AND, three of those four code pages support all of the characters noted in the question: Japanese (932), Chinese Simplified (936), and Korean (949).
CREATE DATABASE [DBCS_test] COLLATE Korean_100_CI_AS; GO USE [DBCS_test]; SELECT 'えましょう';
So, since string literals / variables / parameters use the default Collation of the current / active database, a database change that could be done to fix this situation is:
ALTER DATABASE [DBCS_test] COLLATE Korean_100_CI_AS;
Just keep in mind the following:
-- OR --
Since, again, it is the active / current database (that the query is executing in) that controls the Collation of string literals / variables / parameters, IF your INSERT / UPDATE statement(s) specify a fully-qualified table name (meaning: DatabaseName.SchemaName.TableName ), then an easier approach might be to simply:
CREATE DATABASE [HandleJapaneseCharacters] COLLATE Japanese_XJIS_100_CI_AS_SC;
PLEASE BE AWARE
Please see the following 2-part series of mine regarding exactly how Database-level and Column-level Collations impact non-Unicode string literals and variables / parameters:
For more info on working with Collations, please visit: Collations.info
Answer by Alvin Ramard ·
Answer by Magnus Ahlkvist ·
Short answer is: No, you can't write unicode data without the N-prefix on the string. What happens when you do it without the N-prefix is you get a conversion to varchar from nvarchar if you write it without N-prefix.
So you need to update code to be able to write unicode data into the database. This is a common problem and usually comes from using string-concatenation for variable data instead of parametrizing queries in application level.
When using for example SqlCommand object in .NET, with a parameterized query, .NET takes care of adding the N-prefix for us. And as a bonus, we get application code which is more secure (because it's more SQL Injection safe).