In addition to updating the existing wording, I figured it would be nice to link to the page showing how to string multiple conditions together, just like in the When referencing the Unicode character data types nchar, nvarchar, and ntext, ‘expression’ should be prefixed with the capital letter ‘N’. A Stored Procedure is being used since we will execute it several times. Check Conversions AS SET NOCOUNT ON; SELECT -- "Uni" == Unicode [Latin1_8bit] AS [Latin1], [Hebrew_8bit] AS [Hebrew], [Korean_8bit] AS [Korean], N'█' AS [█], -- visual group separator ( Full Block U 2588 ) IIF([Latin1_8bit] = '₂', 'Match', '') AS [Latin1], IIF([Latin1_Unicode] = '₂', 'Match', '') AS [Latin1_Uni], IIF([Hebrew_8bit] = '₂', 'Match', '') AS [Hebrew], IIF([Hebrew_Unicode] = '₂', 'Match', '') AS [Hebrew_Uni], IIF([Korean_8bit] = '₂', 'Match', '') AS [Korean], IIF([Korean_Unicode] = '₂', 'Match', '') AS [Korean_Uni] FROM dbo. Which Collation Is It; prefixed with a capital-“N” only matches a regular number “2”.If ‘N’ is not specified, SQL Server converts the string to the code page that corresponds to the default collation of the database or column. Which Collation Is It; GO Now that we see what the “Subscript 2” character can be translated into, we should add those two character to our sample data. The documentation said that it would be converted to “the Code Page specified by the Database or column”, but here it is only using the Code Page specified by the Database’s Collation (i.e.The datatype and value of any literal / constant, whether string or numeric, is determined during parsing.Hence, characters are translated (using the Collation of the Database) USE [master]; ALTER DATABASE [Which Collation] COLLATE Hebrew_100_BIN2; USE [Which Collation]; SELECT [name], [collation_name] FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo. Which Collation Is It; the following is returned: This time, it is only the “? This is due to the Hebrew Collations using Code Page 1255 which does not contain “Subscript 2”, and does not have a “Best Fit” mapping for it either.Which Collation Is It; SELECT * FROM #Conversion Test; That returns the following: The referenced column being of a Code Page that contains the character did not help in either case, once again showing that the string literal is translated first, using the Collation of the current Database.Not convinced that the resulting Collation is the column’s Collation?You’ll need to enable OLE Automation on your test server in order to follow along.
We can check what happens when we an explicit Collation is applied.
Yes, that’s it: of those three sentences in the note / warning, The documentation states that it will be one or the other: “the code page that corresponds to the default collation of the database or column”.
” if it doesn’t match itself (due to being transformed). Which Collation Is It VALUES ('2', '2', '2', '2', '2', '2'), -- Possible "best fit" mapping ('? If the Collation of the column being referenced was being used, then “Subscript 2” would still match the “2” in the Latin1 columns, but it would then match the “?
SQL Server has never been short of ways to read from and write to files and it is always better to use the standard techniques provided by SQL Server where possible.
However, most of them are really designed for reading and writing tabular data and aren’t always trouble-free when used with large strings or relatively unstructured data.
SQL Server provides several "standard" techniques by which to read and write to files but, just occasionally, they aren't quite up to the task at hand - especially when dealing with large strings or relatively unstructured data.