Here are the errors: Msg 468, Level 16, State 9, Line 1Ĭannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the CASE operation.Ĭannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the CASE operation.
The reason the COLLATE tests are just selects and not views is because they return errors even when trying to create a view. VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS) AS ISNULL_COLLATE_TEST2 ISNULL(VARCHAR30 COLLATE Latin1_General_CI_AS, VARCHAR30 COLLATE Latin1_General_CI_AS) AS ISNULL_COLLATE_TEST1, ISNULL(VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS, VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS) SELECTĬOALESCE(VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS,ĬOALESCE(VARCHAR30 COLLATE Latin1_General_CI_AS, Since data type was different let’s see what happens with COLLATION. So in the end I’m going to say if the NULLABILITY of your result matters you will just have to pay attention to your specific case. Particularly when I try to match it up with my test results. Which to be very honest I don’t quite follow either. I’m not really sure why it worked out this way and BOL says this:Īn expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. ISNULL on the other hand always returned NOT NULL unless the lower prescendence data type (varchar(10)) was NULLABLE and the first in the list of parameters or both parameters were NULLABLE. COALESCE was always NULLABLE unless both parameters were NOT NULL. Now it get’s a little weird here and I ran a few extra tests on my own, switching the NULL/NOT NULL values on the table back and forth. Next note the Nullable column back on the first sp_help result. This also gives us a rather interesting result if we query the view but let’s consider the result and the why of the result homework. This means that it also has a data types of datetime. And since the ISNULL test where INTEGER was passed in first failed, and we had to remove it from the view, only the test where DATETIME is first is left. This means that COALESCE is going to return a value with the data type of datetime regardless of the order of the parameters. Remember that COALESCE returns a value with the highest precedence data type of the parameter list while ISNULL returns the data type of the first parameter. Use the CONVERT function to run this query. Implicit conversion from data type datetime to int is not allowed. But since it can’t implicitly convert the DATETIME value to INT it returns an error in case the first parameter ends up being NULL. In this case ISNULL wants to return a data type of INT. The first select is not part of the view because it returns an error. SELECT ISNULL(,) AS ISNULL_Test_DT_INT,ĬOALESCE(,) AS COALESCE_Test_DT_INT,ĬOALESCE(, ) AS COALESCE_Test_INT_DTĮXEC sp_help vw_ISNULL_COLLATE_DT_and_INT SELECT ISNULL(, ) AS ISNULL_Test_INT_DTĬREATE VIEW vw_ISNULL_COLLATE_DT_and_INT AS Specifically the ones with data types of DATETIME and INTEGER. To demonstrate that let’s try out the other data types in my test table. You might think “No big deal, I’ve never run into a problem before.” However if you think about it this can have some fairly significant ramifications if for example your parameters are varchar and int. ISNULL on the other hand returns a value with the datatype of the first parameter. Specifically COALESCE returns a value with a datatype of the highest prescedence (in this case the longest varchar) from the list of parameters. You will note that COALESCE has a length of 30 regardless. All of the columns are varchar to simplify things but the lengths are different. The results of the sp_help look like this: ISNULL(VARCHAR10,VARCHAR30) AS ISNULL_Test_10_30,ĬOALESCE(VARCHAR10,VARCHAR30) AS COALESCE_Test_10_30 ISNULL(VARCHAR30,VARCHAR10) AS ISNULL_Test_30_10,ĬOALESCE(VARCHAR30,VARCHAR10) AS COALESCE_Test_30_10, Next, as an easy way to show several differences, I’m going to create a view that uses ISNULL and COALESCE in various ways and then run sp_help on the view. So what are some of the more subtle but still significant differences?įirst let’s create a work table and add some data. The big obvious difference is that ISNULL can only have two parameters while COALESCE can have n parameters. For example COALESCE(fieldname1, fieldname2) and ISNULL(fieldname1, fieldname2) return seemingly identical results. At first glance these two functions seem very similar, and superficially they are.
0 kommentar(er)
