![]() The other interesting aspect is that the string literal is also being converted to the same collation, which is necessary for a correct result. You can see that not only is the latinstr column being converted (explicit CONVERT) to the same collation, it is later being implicitly converted to NVARCHAR. |–Index Scan(OBJECT:(.), This query is very similar to the previous ones, except that it has an explict COLLATE applied to the column. Where latinstr collate latin1_general_ci_as = N’Hello world’ Query 10: Case-insensitive collation specified Similar to the previous case, you see the effect of the GetRangeThroughConvert() internal function to enable the seek despite the data type mismatch. ![]() The main difference is that we are operating on a case-sensitive database. Observations: On the face of it, this query is very similar to the previous query. |–Nested Loops(Inner Join, OUTER REFERENCES:(,, )) Query 8: Data type mismatch in case insensitive collation database The explicit COLLATE clause is effectively ignored by the engine. Observations: This is the ‘best case’ all over again. Where greekstr collate greek_ci_as = ‘Hello world’ Query 7: COLLATE specified with same collation The other interesting thing to observe is the collation conversion of the string literal as well, which allows this comparison to happen. |–Index Scan(OBJECT:(.), In this query, which is kind of the same to the previous query, we convert the Greek column to a 3rd collation (accent-insensitive version of the LatinDb database collation.) The result is that underlying index is not used, causing a scan of the table. Where greekstr collate latin1_general_cs_ai = ‘Hello world’ Query 6: Explicit collation conversion on column From this link, you can see the dreaded words: ‘Data loss during code page translations is not reported.’ While this sounds good, there is potential for data loss. The reason should be obvious: the explicit conversion of the Greek table columns to the Latin collation enables this to work. Observations: This query which is almost the same as previous one, works, while the previous errors out. |–Compute Scalar(DEFINE:(=CONVERT(varchar(50). ![]() |–Nested Loops(Inner Join, OUTER REFERENCES:()) On G.greekstr collate latin1_general_cs_as = L.latinstr Query 5: Usage of COLLATE to specify column collation Observations: This should be obvious from the previous collation rules (if you clicked on the previous link). Result: Msg 468, Level 16, State 9, Line 4Ĭannot resolve the collation conflict between “Latin1_General_CS_AS” and “Greek_CI_AS” in the equal to operation Query 4: Explicit-explicit coercion is not allowed Observations: You see in this case that specifying COLLATE has no effect on the query plan, except that the CONVERT_IMPLICIT changes to a CONVERT (explicit.) The string literal still is coerced into the new collation (GREEK_CI_AS.) Please refer to the collation precedence rules for details (click on the previous link and check the ‘Collation rules’ section) on this. Where greekstr = ‘Hello world’ COLLATE greek_ci_as Query 3: Usage of COLLATE to specify string literal collation The index seek can still be used because the column is not being converted. Due to this, the string literal is converted to the collation of the column. That is what we are seeing here, the string literal ‘hello world’ defaults to the collation of the database, which is in this case is specified as LatinDB. String literals for a batch inherit the database collation (click on the previous link and check the Remarks section) if a COLLATE clause is not specified. Looking at the execution plan, the implicit conversion of the string literal is interesting. The rest of the query operates on greekdb. Observations: Before you read further, keep in mind that USE DATABASE statement, referencing LatinDB. Query 2: Cross database query with collation mismatch ![]() Observations: Fairly obvious results, no collation mismatch, everything is fine, index gets used to seek. |–Index Seek(OBJECT:(.), ORDERED FORWARD) We start by querying these tables and observing the execution plans (I am using text showplan for ease of copying into this blog post.) Next, we are creating some tables in them, inserting data and then building indexes. Let us take some simple examples to demonstrate these points and derive conclusions.įirst, we are setting up 2 databases (inspired by SQL Books Online, these are aptly named GreekDB and LatinDB ). We also hit upon another question: do local variables (DECLARE ) have a collation associated with them and if so how is it controlled? Recently a colleague asked me if SQL collations have any impact on performance.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |