Updating ntext
21-Jan-2020 14:27
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.
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.
BUT, the documentation states “when an nvarchar datatype was referenced”, and yet there is no other datatype in the two queries above. Let’s test what happens when the reference is in the context of string concatenation.
-- DROP TABLE #Conversion Test; SELECT [Korean_8bit] '₂' AS [Should Be Varchar Korean], [Korean_Unicode] '₂' AS [Should Be NVarchar Korean] INTO #Conversion Test FROM dbo.So, the actual question is a bit more specific than would reasonably fit into a title, and it is: I know, I know. ” Well, the reason I asked is because I came across something the other day while I was fixing incorrect and misleading statements in the documentation for the clause via Pull Request #235: “Correct and improve HAVING clause”. To conduct the test, we will use a Stored Procedure that compares the “Subscript 2” character to each combination of the two datatypes and three Collations. '); -- If not in code page and -- no "best fit" mapping SELECT * FROM dbo. Why would anyone even bother asking such a silly question? We will create a Database with one Collation and a Table in that Database with an CREATE DATABASE [Which Collation] COLLATE Latin1_General_100_BIN2; ALTER DATABASE [Which Collation] SET RECOVERY SIMPLE; USE [Which Collation]; -- DROP TABLE dbo. This, again, is why we are using a binary Collation: “Subscript 2” can match a regular number 2, a circled number 2, a superscript 2, etc), and we don’t want false-positives.But, even in the second query, the data in Code Page 949 (used by the Korean Collations).
This is because queries are parsed (for proper syntax, variable name resolution, etc.) before anything is done with the query. 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”.