{"id":1974,"date":"2015-04-02T00:00:00","date_gmt":"2015-04-01T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-get-sql-server-data-conversion-horribly-wrong\/"},"modified":"2021-09-29T16:21:28","modified_gmt":"2021-09-29T16:21:28","slug":"how-to-get-sql-server-data-conversion-horribly-wrong","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/how-to-get-sql-server-data-conversion-horribly-wrong\/","title":{"rendered":"How to Get SQL Server Data-Conversion Horribly Wrong"},"content":{"rendered":"
\n
    \n
  1. Failure #1: Not understanding data type precedence<\/a><\/li>\n
  2. Failure #2: Not taking performance into consideration<\/a><\/li>\n
  3. Failure #3: Assuming all numbers are created equal<\/a><\/li>\n
  4. Failure #4: Relying on the ISNUMERIC built-in function<\/a><\/li>\n
  5. Failure #5: Underestimating the world of silent truncation<\/a><\/li>\n
  6. Failure #6: Not understanding date\/time data<\/a><\/li>\n
  7. Failure #7: Importing Excel data without thought to data types<\/a><\/li>\n
  8. Failure #8: Treating XML just like any other string<\/a><\/li>\n
  9. Failure #9: Failing to take portability into account<\/a><\/li>\n<\/ol>\n

    Failure #1: Not understanding data type precedence<\/h2>\n

    When a T-SQL expression attempts to combine data values of different types, the database engine applies the rules of data type precedence to determine how values should be implicitly converted. If the values cannot be converted, the database engine returns an error. <\/p>\n

    Data type precedence can play a significant role in a variety of operations. If you don’t understand how precedence works, you can end up with errors in places you least expect them-usually at times you can least afford them.<\/p>\n

    But understanding the rules means more than just knowing that DATETIME<\/code><\/b> takes precedence over DECIMAL<\/code><\/b>, and DECIMAL<\/code><\/b> takes precedence over INT<\/code><\/b>, and INT<\/code><\/b> takes precedence over CHAR<\/code><\/b>. Consider the following CASE<\/code><\/b> expression:<\/p>\n

    DECLARE @a CHAR(3) = 'def'\n\nSELECT CASE\n  WHEN @a = 'def' THEN 0\n  WHEN @a = 'ghi' THEN 1\n  ELSE 'does not apply'\nEND;\n\n<\/pre>\n
    In this example, the variable value equals  def<\/code><\/b>, so the first condition in the  CASE<\/code><\/b>  expression evaluates to true and the SELECT<\/code><\/b> statement  returns the value 0<\/code><\/b>. But what  happens when we assign the value  abc<\/code><\/b> to the variable?<\/p>\n
    DECLARE @a CHAR(3) = 'abc'\n\nSELECT CASE\n  WHEN @a = 'def' THEN 0\n  WHEN @a = 'ghi' THEN 1\n  ELSE 'does not apply'\nEND;\n\n<\/pre>\n
    The  SELECT<\/code><\/b> statement now  returns the following error:<\/p>\n
    Msg 245, Level 16, State 1, Line 16\nConversion failed when converting the varchar value 'does not apply' to data type int.\n<\/pre>\n
    The database engine is trying to convert the value  does<\/code><\/b>  not<\/code><\/b>  apply<\/code><\/b> to the  INT<\/code><\/b> data type, and that, of course, doesn’t work. A  CASE<\/code><\/b>  expression returns the type with the highest precedence from the result expressions (the expressions after  THEN<\/code><\/b> and  ELSE<\/code><\/b>).  In this instance, those values include two integers and one string (0<\/code><\/b>, 1<\/code><\/b>, and  does<\/code><\/b> not<\/code><\/b> apply<\/code><\/b>). That means the returned result expression must be an integer  or be convertible to an integer. It doesn’t matter that the two conditions in the  CASE<\/code><\/b> expression  evaluate to false. All the database engine cares about is that an integer takes precedence over a character data type.<\/p>\n
    One way to address this issue is to treat all the result expressions as strings:<\/p>\n
    DECLARE @a CHAR(3) = 'abc'\n\nSELECT CASE\n  WHEN @a = 'def' THEN '0'\n  WHEN @a = 'ghi' THEN '1'\n  ELSE 'does not apply'\nEND;\n\n<\/pre>\n
    Now the CASE<\/b> expression returns the value  does<\/code><\/b> not<\/code><\/b> apply<\/code><\/b>. By specifying the result expressions as strings, we’ve put  them on equal footing in the eyes of the precedence gods. <\/p>\n
    For our example here, enclosing the values in single quotes did the trick, but in many  situations, you’ll likely have to use  CAST<\/code><\/b> or  CONVERT<\/code><\/b> to explicitly convert the values, such as when you pass in  variables or columns. The point is, you have to understand how data is converted and what that means to your  expressions. Otherwise, you can wind up with a nightly batch process that fails every third run for no apparent reason.<\/p>\n
    Such issues are hardly limited to  CASE<\/code><\/b> expressions. Let’s take a look at the  COALESCE<\/code><\/b>  function in action:<\/p>\n
    DECLARE\n  @a CHAR(3) = 'abc',\n  @b CHAR(5) = 'defgh',\n  @c VARCHAR(10) = NULL,\n  @d INT = 1234;\n\nSELECT COALESCE(@c, @d, @a);\n\n<\/pre>\n
    We declare a set of variables and then use  COALESCE<\/code><\/b> to return the first value that does not evaluate to  NULL<\/code><\/b>.  As expected, the  SELECT<\/code><\/b> statement returns 1234<\/code><\/b>.  Now let’s switch the variable order:<\/p>\n
    SELECTCOALESCE(@c, @a, @d);<\/pre>\n
    This time, the statement generates an error, even though the  @a<\/code><\/b>  variable contains a non-NULL<\/code><\/b>  value:<\/p>\n
    Msg 245, Level 16, State 1, Line 88\nConversion failed when converting the varchar value 'abc' to data type int.\n\t<\/pre>\n
    Once again, the database engine is trying to convert data and the conversion is failing.  Similar to a CASE<\/code><\/b> expression, COALESCE<\/code><\/b>  returns the data type of the expression with the highest precedence. The  @d<\/code><\/b> variable is defined as  an INT<\/code><\/b>,  which has precedence of the other variable types. As a result, any value returned by the function, other than  NULL<\/code><\/b>, must be an  integer or convertible to one.<\/p>\n
    The way around this is, of course, to use  CONVERT<\/code><\/b> or  CASE<\/code><\/b> to explicitly convert the integer to a character data type:<\/p>\n
    SELECTCOALESCE(@c, @a,CONVERT(VARCHAR(10), @d));<\/pre>\n
    Now the  SELECT<\/code><\/b> statement will  return a value of abc<\/code><\/b>. That said,  it’s not enough to simply convert numeric data to a string. Take a look at what happens when we mix things up again:<\/p>\n
    SELECT COALESCE(@c, @a, @b) AS FirstValue,\n  SQL_VARIANT_PROPERTY(COALESCE(@c, @a, @b), 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(COALESCE(@c, @a, @b), 'maxlength') AS TypeLength;\n\t<\/pre>\n
    The  SQL_VARIANT_PROPERTY<\/code><\/b>  function lets us view details about the value being returned. As the following results show, the  @a<\/code><\/b>  value is returned as  VARCHAR(10)<\/code><\/b>:<\/p>\n\n\n\n\n
    \n
     \t\tFirstValue<\/b><\/p>\n<\/td>\n
    		\n
    ValueType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypeLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    abc<\/p>\n<\/td>\n
    		\n
    varchar<\/p>\n<\/td>\n
    		\n
    10<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Although the VARCHAR<\/code><\/b> data type  adds only a couple bytes per value, those bytes can add up to a significant amount if we’re talking billions of rows,  especially if those rows are sitting in memory.<\/p>\n
    The  ISNULL<\/code><\/b> function is another  one that can cause unexpected issues. The function replaces the first value with the second value if the first value is NULL<\/code><\/b>, as shown in the following example:<\/p>\n
    DECLARE\n  @a CHAR(3) = 'abc',\n  @b CHAR(2) = NULL,\n  @c INT = 1234,\n  @d INT = NULL;\n\nSELECT ISNULL(@d, @c);\n<\/pre>\n
    Because the @d<\/code><\/b> variable is NULL<\/code><\/b>,  the SELECT<\/code><\/b>  statement returns a value of 1234<\/code><\/b>. Now suppose  we specify  @a<\/code><\/b> as the second value:<\/p>\n
    SELECTISNULL(@d, @a);<\/pre>\n
    Once again, the database engine generates a conversion error:<\/p>\n
    Msg 245, Level 16, State 1, Line 159\nConversion failed when converting the varchar value 'abc' to data type int.\n<\/pre>\n
    Unless you pass in a literal  NULL<\/code><\/b> as the first expression,  ISNULL<\/code><\/b> uses that expression’s type for the returned value. In this  case, the type is  INT<\/code><\/b>, which means the database engine is trying to convert  abc<\/code><\/b>  to a numeric type. Not only can this lead to an error, as we received here, but it can also lead to an odd sort of  truncation:<\/p>\n
    SELECT ISNULL(@b, @c) FirstValue,\n  SQL_VARIANT_PROPERTY(ISNULL(@b, @c), 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(ISNULL(@b, @c), 'maxlength') AS TypeLength;\n\n<\/pre>\n
    The first value this time around is of the type  CHAR(2)<\/code><\/b>. When we try to convert the  @c<\/code><\/b>  value to  CHAR<\/code><\/b>, we don’t get an error, but rather an asterisk:<\/p>\n\n\n\n\n
    \n
     \t\tFirstValue<\/b><\/p>\n<\/td>\n
    		\n
    ValueType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypeLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    * <\/p>\n<\/td>\n
    		\n
    char<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The problem is not one of a failed implicit conversion, but rather one of trying to turn an INT<\/code><\/b> value into a CHAR(2)<\/code><\/b>  value. If it were  CHAR(4)<\/code><\/b>, the conversion  would be fine. Instead, we end up in conversion limbo, a likely carryover from the early days of handling database  overflow errors, before error handling got a more reputable foothold. Imagine trying to insert asterisks into those data  warehouse columns configured with the  INT<\/code><\/b> data type.<\/p>\n
    You should learn how data type precedence works and how it is applied before<\/i> these types of problems arise. A good place to start is with the MSDN  topic “Data Type Precedence<\/a>.” But don’t stop there.  You should also know how your expressions, functions, and other elements treat data when it is implicitly converted.  Your best strategy is to explicitly convert the data when you know a conversion is imminent and to provide the logic  necessary to handle different possible scenarios. Otherwise, you leave yourself open to chance, which seldom works as a  long-term strategy.<\/p>\n
    Failure #2: Not taking performance into  consideration<\/h2>\n
    Not only can implicit conversions wreak havoc by generating unexpected errors (or those  pseudo-error asterisks), but they also take their toll on performance. Let’s look at an example of a basic  SELECT<\/code><\/b> statement that retrieves data from the   AdventureWorks2014<\/code><\/b> sample database:<\/p>\n
    SET STATISTICS IO ON;\nSELECT BusinessEntityID, LoginID\nFROM HumanResources.Employee\nWHERE NationalIDNumber = 948320468;\nSET STATISTICS IO OFF; \n\t<\/pre>\n
    The statement includes a WHERE<\/code><\/b> clause that  specifies a  NationalIDNumber<\/code><\/b> value,  which is stored in the Employee<\/code><\/b> table as  NVARCHAR(15)<\/code><\/b> data. Because we’re capturing the I\/O statistics, we receive the following  information as part of our results:<\/p>\n
    Table 'Employee'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/pre>\n
    There are two important statistics worth noting here. The first is that an index scan is  being performed, rather than a seek, and the second is that it takes six logical reads to retrieve the data. If we  generate an execution plan when we run the query, we can view information about the scan by hovering over the scan icon  in the execution plan. The following figures shows the details about the scan:<\/p>\n
      \"2166-clip_image002.gif\"<\/p>\n
    First, take a look at the Predicate<\/code><\/b> section.  The database engine is using the  CONVERT_IMPLICIT<\/code><\/b> function to convert the  NationalIDNumber<\/code><\/b>  value in order to compare it to the 948320468<\/code><\/b> value.  That’s because we’re passing the value in as an integer, so the database engine must implicitly convert the column value  to an INT<\/code><\/b> to do the comparison.<\/p>\n
    Now let’s rerun the statement, only pass the  NationalIDNumber<\/code><\/b> value in as a string:<\/p>\n
    SET STATISTICS IO ON;\nSELECT BusinessEntityID, LoginID\nFROM HumanResources.Employee\nWHERE NationalIDNumber = '948320468';\nSET STATISTICS IO OFF;\n<\/pre>\n
    This time, our statistics show that the database engine performs no scans and only four  logical reads:<\/p>\n
    Table 'Employee'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/pre>\n
    If we generate the execution plan, we can view details about the seek, which shows a  conversion, but only in terms of data length, with no scan performed. We even get better statistics in operator, I\/O,  and CPU costs.<\/p>\n
      \"2166-clip_image004.gif\"<\/p>\n
    This, of course, is only one query retrieving one row based on one value. But start  multiplying those values and rows and queries and you can end up with monstrous performance hits because you’re not  paying attention to how your data is being converted.<\/p>\n
    Failure #3: Assuming all numbers are created equal<\/h2>\n
    Numerical data likes to play tricks on us, especially when implicit conversions are involved.  If we’re not careful, we can end up with results we don’t expect or want, often without any hint that there’s a problem.<\/p>\n
    Take, for example, the following T-SQL, which converts decimal data to integer data:<\/p>\n
    DECLARE\n  @a INT = NULL,\n  @b DECIMAL(5,2) = 345.67;\nSET @a = @b;\nSELECT @a;\n\n<\/pre>\n
    You might expect SQL Server to handle this gracefully and round the  345.56<\/code><\/b>  to 346<\/code><\/b>.  It does not. Instead, the  SELECT<\/code><\/b> statement returns a  value of 345<\/code><\/b>. The database  engine simply truncates the value, without any attempt at rounding.<\/p>\n
    What appears as only a slight loss here can translate to big losses to the bottom line.  Suppose the original decimal value refers to shipping weights. If customers should be charged based on the next highest  whole number, but your database is always truncating the value, someone is going to have to eat the costs for all the  weight that’s not been accounted for.<\/p>\n
    There are ways to address such situations. For example, you might use the  CEILING<\/code><\/b>  function to round the value up:<\/p>\n
    DECLARE\n  @a INT = NULL,\n  @b DECIMAL(5,2) = 345.67;\nSET @a = CEILING(@b);\nSELECT @a;\n\n<\/pre>\n
    Now the  SELECT<\/code><\/b> statement returns a  value of 346<\/code><\/b>, an amount  sure to keep the accounting department happy. However, other issues await. Let’s look at what happens when we try to add  a decimal and integer together:<\/p>\n
    DECLARE\n  @a INT = 12345,\n  @b DECIMAL(5,2) = 345.67;\nSELECT @a + @b AS Total,\n  SQL_VARIANT_PROPERTY(@a + @b, 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(@a + @b, 'precision') AS TypePrecision,\n  SQL_VARIANT_PROPERTY(@a + @b, 'scale') AS TypeScale;\n<\/pre>\n
    Because of data type precedence, SQL Server converts the integer to a decimal and then adds  the two together. Although the database engine handles the conversion without a hiccup, it does increase the precision:<\/p>\n\n\n\n\n
    \n
    Total<\/b><\/p>\n<\/td>\n
    		\n
    ValueType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypePrecision<\/b><\/p>\n<\/td>\n
    		\n
    TypeScale<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    12690.67<\/p>\n<\/td>\n
    		\n
    decimal<\/p>\n<\/td>\n
    		\n
    13<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The increased precision might not seem a big deal, but it can add up. According to SQL Server  documentation, a decimal with a precision from 1 through 9 requires five bytes of storage. A decimal with a precision of  10 through 19 requires nine bytes of storage.<\/p>\n
    You need to understand how precision works whenever you’re converting numeric data. Not only  do you risk extra overhead, but you could also end up with a less-than-happy database engine. Let’s recast the last  example in order to insert the sum into a table variable:<\/p>\n
    DECLARE\n  @a INT = 12345,\n  @b DECIMAL(5,2) = 345.67;\n\nDECLARE @c TABLE(ColA DECIMAL(5,2));\n\nINSERT INTO @c(ColA)\nSELECT @a + @b;\n\n<\/pre>\n
    When we try to insert the data, we receive the following error:<\/p>\n
    Msg 8115, Level 16, State 8, Line 258\nArithmetic overflow error converting numeric to data type numeric.\nThe statement has been terminated.\n\t<\/pre>\n
    If variable @a<\/code><\/b> had been a  smaller number, such as 123<\/code><\/b>, we would have  received no error. The same is true if we change the precision of  ColA<\/code><\/b> to match to match the  sum, in which case the insert will run with no problem:<\/p>\n
    DECLARE\n  @a INT = 12345,\n  @b DECIMAL(5,2) = 345.67;\n\nDECLARE @c TABLE(ColA DECIMAL(7,2));\n\nINSERT INTO @c(ColA)\nSELECT @a + @b;\n\nSELECT ColA,\n  SQL_VARIANT_PROPERTY(ColA, 'basetype') AS ColType,\n  SQL_VARIANT_PROPERTY(ColA, 'precision') AS TypePrecision,\n  SQL_VARIANT_PROPERTY(ColA, 'scale') AS TypeScale\nFROM @c;\n<\/pre>\n
    As the following results show, the  ColA<\/code><\/b> value is now configured as  DECIMAL(7,2)<\/code><\/b>:<\/p>\n\n\n\n\n
    \n
    ColA<\/b><\/p>\n<\/td>\n
    		\n
    ColType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypePrecision<\/b><\/p>\n<\/td>\n
    		\n
    TypeScale<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    12690.67<\/p>\n<\/td>\n
    		\n
    decimal<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The point of all this is that you must be prepared to handle whatever type of numeric data  comes your way, which means you need to understand how the numeric data types work in SQL Server, especially when you  start converting data.<\/p>\n
    Let’s look at another example of what might go wrong. In the following T-SQL, we compare REAL<\/code><\/b>  and INT<\/code><\/b>  values:<\/p>\n
    DECLARE\n  @a INT = 100000000,\n  @b INT = 100000001,\n  @c REAL = NULL;\nSET @c = @b;\nSELECT CASE\n  WHEN @a = @c THEN 'values equal'\n  ELSE 'values not equal'\nEND AS CheckEquality,\nSTR(@c, 30, 20) AS VarCValue;\n<\/pre>\n
    In this case, we’re implicitly converting the  @b<\/code><\/b> integer to a  REAL<\/code><\/b> value and then comparing that value to the  @a<\/code><\/b>  integer, using a  CASE<\/code><\/b> expression to test  for equality. Based on the original values of the two integers, we might expect the  CASE<\/code><\/b> expression to  return  values<\/code><\/b>  not<\/code><\/b>  equal<\/code><\/b>. Instead, we get the following results:<\/p>\n\n\n\n\n
    \n
     \t\tCheckEquality<\/b><\/p>\n<\/td>\n
    		\n
    VarCValue<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    values equal<\/p>\n<\/td>\n
    		\n
    100000000.0000000000000000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The  STR<\/code><\/b> function let’s us  easily view the actual value being stored in the  @c<\/code><\/b> variable, rather than scientific notation. As you can see, there  is no hint of the  1<\/code><\/b> that was there before we converted the data. The problem is that  the REAL<\/code><\/b> data type,  like the  FLOAT<\/code><\/b> data type, is considered an approximate-number data type, which means not all values  in the permitted range can be represented exactly. If you plan to compare or convert  REAL<\/code><\/b>  or FLOAT<\/code><\/b>  data, you better understand the limitations of those types. Otherwise that rocket you’re sending to the next passing  asteroid might end up boldly going where no one has gone before<\/p>\n
    Also be aware of how SQL Server handles numeric data when used in conjunction with  non-numeric data, particularly when trying to add or concatenate values. For example, if you try to add two values in  which one is an integer and one is a string, the database engine implicitly converts the string type to the numeric type  and adds the values together:<\/p>\n
    DECLARE\n  @a INT = 123,\n  @b CHAR(3) = '456';\nSELECT @a + @b AS EndValue,\n  SQL_VARIANT_PROPERTY(@a + @b, 'basetype') AS BaseType,\n  SQL_VARIANT_PROPERTY(@a + @b, 'maxlength') AS TypeLength;\n<\/pre>\n
    As the following results show, the two values are added together and an integer is returned:<\/p>\n\n\n\n\n
    \n
    EndValue<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypeLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    579<\/p>\n<\/td>\n
    		\n
    int<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The database engine converts the character data to an integer because the  INT<\/code><\/b>  data type takes precedence over the CHAR<\/code><\/b> data type. If  what you’re actually after is to concatenate the two values, then you must explicitly convert the integer to a string:<\/p>\n
    DECLARE\n  @a INT = 123,\n  @b CHAR(3) = '456';\nSELECT CONVERT(CHAR(3), @a) + @b AS EndValue,\n  SQL_VARIANT_PROPERTY(CONVERT(CHAR(3), @a) + @b, \n    'basetype') AS BaseType,\n  SQL_VARIANT_PROPERTY(CONVERT(CHAR(3), @a) + @b, \n    'maxlength') AS TypeLength;\n<\/pre>\n
    Now the results show the concatenated value and the  CHAR<\/code><\/b> data type:<\/p>\n\n\n\n\n
    \n
    EndValue<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypeLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    123456<\/p>\n<\/td>\n
    		\n
    char<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Differentiating between adding values and concatenating values, like any aspect of numerical  data, requires that you understand how numeric data types work, how data type precedence works, and how T-SQL elements  work with numeric data. Otherwise, you can never be sure you’re getting the results you had actually expected.<\/p>\n
    Failure #4: Relying on the  ISNUMERIC<\/code>  built-in function<\/h2>\n
    One T-SQL element in particular that can catch developers off-guard is the  ISNUMERIC<\/code><\/b>  function. The function tests an expression to determine whether it produces a numeric type. If it does, the function  returns the value  1<\/code><\/b>; otherwise, it returns a  0<\/code><\/b>. The challenge with this function is that it can sometimes  interpret a value as numeric even if it contains no numbers.<\/p>\n
    Let’s look at an example. The following T-SQL creates a table variable, adds an assortment of  string values to the variable, and then uses a  CASE<\/code><\/b> expression to test  whether those values are considered numeric:<\/p>\n
    DECLARE @a TABLE(ColA VARCHAR(10));\nINSERT INTO @a VALUES\n('abc'), ('123'), ('$456'), \n('7e9'), (','), ('$.,');\n\nSELECT colA, CASE\n  WHEN ISNUMERIC(colA) = 1 \n    THEN CAST(colA AS INT)\n  END AS TestResults\nFROM @a;\n<\/pre>\n
    If a value is numeric, the SELECT<\/code><\/b> statement  tries to convert the value to the  INT<\/code><\/b> data type; otherwise, the statement returns a  NULL<\/code><\/b>.  Unfortunately, when the CASE<\/code><\/b> expression  bumps up against the value $456<\/code><\/b>, the database  engine generates the following error:<\/p>\n
    Msg 245, Level 16, State 1, Line 308\nConversion failed when converting the varchar value '$456' to data type int.\n\t<\/pre>\n
    The  ISNUMERIC<\/code><\/b> function is  actually quite liberal when deciding what constitutes a numeric value. In this case, it sees a dollar sign and  interprets the value as numeric, yet when the  CASE<\/code><\/b> expression tries to  convert the value to an integer, the database engine baulks. <\/p>\n
    To get a better sense of what the function considers to be numeric, let’s recast our  SELECT<\/code><\/b>  statement:<\/p>\n
    SELECT ColA, ISNUMERIC(ColA) AS TestResults\nFROM @a;\n<\/pre>\n
    As the following results show, the  ISNUMERIC<\/code><\/b> function interprets all values except  abc<\/code><\/b>  as numeric:<\/p>\n\n\n\n\n\n\n\n\n\n
    \n
    ColA<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTestResults<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    abc<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    123<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    $456<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    7e9<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    ,<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    $.,<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    For the value $456<\/code><\/b> it’s easy to  see how the function can interpret this as money and consequently a numeric type. The next value,  7e9<\/code><\/b>, also makes  sense because the function sees it as scientific notation. What is not so clear is why the last two values are  considered numeric. If such values are possible in your data set, relying on the  ISNUMERIC<\/code><\/b> function to control your statement’s logic when converting  data can lead to an assortment of problems.<\/p>\n
    If you’re running SQL Server 2012 or later, you can instead use the  TRY_CONVERT<\/code><\/b>  function to test your values before converting them:<\/p>\n
    DECLARE @a TABLE(ColA VARCHAR(10));\nINSERT INTO @a VALUES\n('abc'), ('123'), ('$456'), \n('7e9'), (','), ('$.,');\n\nSELECT ColA, CASE\n  WHEN TRY_CONVERT(int, ColA) IS NULL \n    THEN 0 ELSE 1\nEND AS TestResults\nFROM @a;\n<\/pre>\n
    If the value cannot be converted to an integer, the  CASE<\/code><\/b> expression returns the value  0<\/code><\/b>;  otherwise, it returns a  1<\/code><\/b>, as shown in the  following results:<\/p>\n\n\n\n\n\n\n\n\n\n
    \n
    ColA<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTestResults<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    abc<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    123<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    $456<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    7e9<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    ,<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    $.,<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    This time we have a more reliable assessment of the data. Unfortunately, if you’re running a  version of SQL Server prior to 2012, you’ll have to come up with another way to check for numeric values before trying  to convert them. Just be careful not to rely on the  ISNUMERIC<\/code><\/b> function alone unless you’re certain about the  predictability of the data you’ll be converting.<\/p>\n
    Failure #5: Underestimating the world of silent  truncation<\/h2>\n
    If a value will be truncated when inserting it into a column, the database engine returns an  error, warning you of the possible truncation. Unfortunately, the database engine is not so diligent in all cases,  particularly when it comes to variables and parameters. One wrong move and you can end up with a database full of  truncated data and a recovery scenario that leaves you without sleep for the next three months.<\/p>\n
    Let’s look at a simple example of what can happen:<\/p>\n
    DECLARE\n  @a CHAR(6) = 'abcdef',\n  @b CHAR(3) = NULL;\nSET @b = @a\nSELECT @b AS VarValue,\n  SQL_VARIANT_PROPERTY(@b, 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(@b, 'maxlength') AS TypeLength;\n<\/pre>\n
    When we attempt to set the value of  @b<\/code><\/b> to  @a<\/code><\/b>, the database engine happily obliges, as evidenced by the  SELECT<\/code><\/b> statement’s  results:<\/p>\n\n\n\n\n
    \n
    VarValue<\/b><\/p>\n<\/td>\n
    		\n
    ValueType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypeLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    abc<\/p>\n<\/td>\n
    		\n
    char<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The original value, abcdef<\/code><\/b>, has been  seamlessly truncated to conform to the  CHAR(3)<\/code><\/b> type. The same thing can happen if we run an  ISNULL<\/code><\/b>  function against the values:<\/p>\n
    SELECT ISNULL(@b, @a) AS VarValue,\n  SQL_VARIANT_PROPERTY(ISNULL(@b, @a), 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(ISNULL(@b, @a), 'maxlength') AS TypeLength;\n<\/pre>\n
    The statement returns the same results as the preceding  SELECT<\/code><\/b>,  with the original value truncated. Unless we pass in a literal  NULL<\/code><\/b> as the first expression,  ISNULL<\/code><\/b> uses the type of the first expression, which in this case is <\/b>CHAR(3)<\/code><\/b>.<\/p>\n
    Parameters too can fall victim to the sinister world of silent truncation:<\/p>\n
    CREATE TABLE #a (ColA CHAR(5));\n\nIF OBJECT_ID('ProcA', 'P') IS NOT NULL\nDROP PROCEDURE ProcA;\nGO\n \nCREATE PROCEDURE ProcA @a VARCHAR(5)\nAS\nINSERT INTO #a(ColA) VALUES(@a);\nGO\n<\/pre>\n
    The target column in the #a<\/code><\/b> temporary table  is configured as CHAR(5)<\/code><\/b> and the  stored procedure’s parameter @a<\/code><\/b> as  VARCHAR(5)<\/code><\/b>,  which would suggest no room from truncation. However, unless you explicitly check the parameter’s input value, you could  run into problems. For example, suppose we pass in a value larger that what the parameter supports when calling the  procedure: <\/p>\n
    EXEC ProcA 'ab cd ef gh';\n\nSELECT * FROM #a;\n<\/pre>\n
    The database engine will silently truncate the value and insert a shortened version, giving  us a returned value of  ab<\/code><\/b> cd<\/code><\/b>, all without  any sort of error.<\/p>\n
    Another issue to watch for is if ANSI warnings are turned off during your insert operations.  By default, the warnings are turned on, which is why the database engine generates an error if a value will be truncated  when inserting it into a column:<\/p>\n
    DECLARE @a CHAR(5) = 'abcde';\n\nCREATE TABLE #a (ColA CHAR(3));\nINSERT INTO #a VALUES(@a);\n<\/pre>\n
    By default, the statement generates the following error:<\/p>\n
    Msg 8152, Level 16, State 14, Line 437\nString or binary data would be truncated.\nThe statement has been terminated.\n<\/pre>\n
    However, it’s not uncommon to set ANSI warnings to off in certain cases, such as bulk load  operations:<\/p>\n
    SET ANSI_WARNINGS OFF;\n\nDECLARE @b CHAR(5) = 'abcde';\n\nCREATE TABLE #b (ColB CHAR(3));\nINSERT INTO #b VALUES(@b);\n\nSELECT ColB FROM #b;\n\nSET ANSI_WARNINGS ON;\n<\/pre>\n
    This time, the database engine does not return an error. It simply truncates the data and  sticks what’s left into the table, giving us the value  abc<\/code><\/b>.<\/p>\n
    You must be vigilant against silent truncations when data is being converted from one type to  another, even if it’s only a smaller size of the same type. Truncations can and do occur without anyone realizing what  has happened-until it’s too late.<\/p>\n
    Failure #6: Not understanding date\/time data<\/h2>\n
    Date\/time values in SQL Server can be full of surprises when converting data, often because  of the format used to pass in the date. For instance, the following T-SQL tries to convert a date that follows the  day-month-year format: <\/p>\n
    DECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '21\/3\/2015';\nSET @a = @b;\nSELECT @a;\n<\/pre>\n
    When the database engine tries to convert the string, it returns an out-of-range error:<\/p>\n
    Msg 242, Level 16, State 3, Line 582\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\n<\/pre>\n
    In this case, the SQL Server instance is configured to use US English, but the date conforms  to British and French standards. Suppose we recast the date as follows:<\/p>\n
    DECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '3\/21\/2015';\nSET @a = @b;\nSELECT @a;\n<\/pre>\n
    The  SELECT<\/code><\/b> statement now  returns the following results:<\/p>\n
    2015-03-21 00:00:00.000<\/pre>\n
    SQL Server follows specific date\/time conventions based on the configured language. Look at  what happens when we change the language:<\/p>\n
    SET LANGUAGE british;\nDECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '21\/3\/2015';\nSET @a = @b;\nSELECT @a;\n\t<\/pre>\n
    The database engine converts the date with no problem, and our  SELECT<\/code><\/b>  statement returns the value we expect. Now let’s run the T-SQL again, only change the language to US English:<\/p>\n
    SET LANGUAGE us_english;\nDECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '21\/3\/2015';\nSET @a = @b;\nSELECT @a;\n\t<\/pre>\n
    This time around, we receive an out-or-range conversion error because the date follows the  day-month-year convention. If you’re not prepared for these differences, you can end up with out-of-range conversion  errors all over the place.<\/p>\n
    One way to address this issue is to use the  CONVERT<\/code><\/b> function to specifically convert the string value to the  necessary format:<\/p>\n
    DECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '21\/3\/2015';\nSET @a = CONVERT(DATETIME, @b, 103);\nSELECT @a;\n\t<\/pre>\n
    The third argument in the CONVERT<\/code><\/b> function, 103<\/code><\/b>,  specifies that the value to be converted should be in the British\/French style. As a result, the  SELECT<\/code><\/b> statement  will now return our date\/time value as expected. <\/p>\n
    Another approach is to set the  DATEFORMAT<\/code><\/b> property. Normally, the selected language determines the  property’s value, but we can override it:<\/p>\n
    SET DATEFORMAT dmy;\n\nDECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '21\/3\/2015';\nSET @a = @b;\nSELECT @a;\n<\/pre>\n
    We’ve set the DATEFORMAT<\/code><\/b>  property to  dmy<\/code><\/b> for day-month-year.  The British\/French version of the date can now be converted with no problem, and if we want to return to the US English  format, we can change the property setting once again:<\/p>\n
    SET DATEFORMAT mdy;\n\nDECLARE\n  @a DATETIME = NULL,\n  @b VARCHAR(10) = '3\/21\/2015';\nSET @a = @b;\nSELECT @a;\n\n<\/pre>\n
    Using the  CONVERT<\/code><\/b> function or  setting the  DATEFORMAT<\/code><\/b> property are  fine approaches if we know the source language. If we have no way to determine the source of the data within the  database, it makes this process more difficult, and we have to rely on the application to tell us the language or to  enforce a particular format.<\/p>\n
    If you can determine the language based on the connection to the data source, you can change  your settings to target that language before importing the data, and then change the language back when the import is  complete. Robyn Page offers great insight into this and other issues related to date\/time values and languages in her  article “Robyn  Page’s SQL Server DATE\/TIME Workbench<\/a>.”<\/p>\n
    Also be aware that you can run into issues when converting data from one date\/time type to  another. Suppose you’re trying to convert  DATETIME<\/code><\/b> data to  SMALLDATETIME<\/code><\/b>:<\/p>\n
    DECLARE \n  @a DATETIME = '3\/21\/2099',\n  @b SMALLDATETIME = NULL;\nSET @b = @a;\nSELECT @b;\n\n<\/pre>\n
    Notice the year: 2099. This works fine for the  DATETIME<\/code><\/b> data type, but not  SMALLDATETIME<\/code><\/b>. When we try to convert the data, the database engine  generates the following out-or-range conversion error:<\/p>\n
    Msg 242, Level 16, State 3, Line 740\nThe conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value.\n\t<\/pre>\n
    The  DATETIME<\/code><\/b> data type  supports the years 1753 through 9999, but  SMALLDATETIME<\/code><\/b> goes only from 1900 through 2079. Perhaps for many of  your needs, the smaller range is enough. But all it takes is one out-of-range year to bring your bulk load operation to  a halt.<\/p>\n
    Even when you can convert a date from  DATETIME<\/code><\/b> to  SMALLDATETIME<\/code><\/b>, you should also take into account the loss of  precision in seconds:<\/p>\n
    DECLARE\n  @a DATETIME = GETDATE(),\n  @b SMALLDATETIME = NULL;\nSET @b = @a;\nSELECT @a AS VarA, @b AS VarB;\n<\/pre>\n
    The following table shows the results returned by the  SELECT<\/code><\/b> statement:<\/p>\n\n\n\n\n
    \n
    VarA<\/b><\/p>\n<\/td>\n
    		\n
    VarB<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2015-03-18 11:33:10.560<\/p>\n<\/td>\n
    		\n
    2015-03-18 11:33:00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Notice that we lose over 10 seconds when converting from  DATETIME<\/code><\/b>  to  SMALLDATETIME<\/code><\/b>. The database engine essentially truncates the value at the minute mark and  returns only zeroes for the seconds. A few seconds here or there might not seem much, but they can add up and impact  analytical processes that require precise calculations down to the hundredth of a second.<\/p>\n
    Whenever you’re working with date\/time values, you have to take their nature into account.  Consider the following example:<\/p>\n
    DECLARE\n  @a DATETIME2 = '2015-03-12 11:28:19.7412345',\n  @b DATETIME,\n  @c SMALLDATETIME,\n  @d VARCHAR(30);\n\nSET @b = @a;\nSET @c = @a;\nSET @d = @a;\n\nSELECT @a, @b, @c, @d;\n<\/pre>\n
    The  SELECT<\/code><\/b> statement returns  the following results (formatted as a list for easy viewing):<\/p>\n
    2015-03-12 11:28:19.7412345\n2015-03-12 11:28:19.740\n2015-03-12 11:28:00\n2015-03-12 11:28:19.7412345\n\t<\/pre>\n
    When converting data to or from a date\/time data type, you should have a good sense of how  the data types work and conversions work and what can happen to your values-before you start tracking data for the next  Olympic trials.<\/p>\n
    Failure #7: Importing Excel data without thought to  data types<\/h2>\n
    Importing Excel data can be tricky unless you know exactly what types of data the spreadsheet  contains and you’re certain the data types will always be the same. But if you’re importing data from an Excel  spreadsheet on a regular basis, and that spreadsheet is continuously updated, you run the risk of data types changing,  which can making converting the data unpredictable, unless you’re prepared for the possible changes.<\/p>\n
    Here’s the problem. When you import Excel data into SQL Server, the OLE DB provider guesses  at a column’s data type by sampling a set of rows and going with the majority. By default, that sample is made up of the  spreadsheet’s first eight rows. For example, if a column contains five numeric values in the first eight rows, the  provider assigns the  FLOAT<\/code><\/b> data type to that column (even if all numbers are integers).  When this occurs, any non-numeric values are returned as  NULL<\/code><\/b>.<\/p>\n
    Although, there are ways to work around this limitation, such as adding the  IMEX<\/code><\/b>  property to the provider string and setting its value to  1<\/code><\/b>, the issue points to another challenge. Under certain  circumstances, the provider can return different data types for the same column, depending on how the data has been  updated between import operations. If you’re T-SQL statements convert the data as it’s coming into the database, you  need to include the logic necessary to handle the possibility of changing types. <\/p>\n
    For instance, suppose you use a  SELECT...INTO<\/code><\/b> statement and the  OPENROWSET<\/code><\/b> function to retrieve data from an Excel spreadsheet and  load it into a temporary table. You also define the provider string to take into account the possibility of a column  containing mixed types. As to be expected, the provider will determine the table’s column types based on the sampled  values in the spreadsheet. <\/p>\n
    One of the columns is supposed to contain only integers, but occasionally alphanumeric values  find there way into the column. As a result, the provider will create the corresponding column in the temporary table as  either  FLOAT<\/code><\/b> or  VARCHAR(255)<\/code><\/b>, depending on  the balance in the sample. Let’s look at temporary table initially set up with a single  VARCHAR<\/code><\/b> column:<\/p>\n
    CREATE TABLE #a (ColA VARCHAR(255));\nINSERT INTO #a VALUES\n('101'), ('102ex'), ('103'), ('1o4');\n\t<\/pre>\n
    Suppose that ColA<\/code><\/b> is usually  configured with the INT<\/code><\/b> data type, but  in this case, it was assigned the  VARCHAR<\/code><\/b> data type to accommodate those wayward string values. If any  T-SQL code contains logic relying on the column being  INT<\/code><\/b>, our statements could  fail, which might be particularly confusing if we don’t understand how the OLE DB provider determines data types.<\/p>\n
    To safeguard against this issue, we need to incorporate the logic necessary to handle the  possibility of changing types: <\/p>\n
    SELECT ColA, CASE\n  WHEN TRY_CONVERT(INT, ColA) IS NOT NULL \n    THEN CAST(ColA AS INT)\n  ELSE 0\nEND AS ColA\nFROM #a;\n\t<\/pre>\n
    In this case, we’re saying that, if the data isn’t right, set the value to  0<\/code><\/b>  until we can fix it; otherwise, convert the value to an integer, giving us the following results:<\/p>\n\n\n\n\n\n\n\n
    \n
    ColA<\/b><\/p>\n<\/td>\n
    		\n
    CheckA<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    101<\/p>\n<\/td>\n
    		\n
    101<\/p>\n<\/td>\n<\/tr>\n
    \n
    102ex<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    103<\/p>\n<\/td>\n
    		\n
    103<\/p>\n<\/td>\n<\/tr>\n
    \n
    1o4<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    You might take a different approach, of course, or many different approaches. What’s  important here is that, unless you can rely on your Excel spreadsheets to always provide the same types of data, you  have to be prepared for the possibility of unexpected changes.<\/p>\n
    Failure #8: Treating XML just like any other string<\/h2>\n
    Converting string data to XML is relatively painless as long as the string is well formed  XML, in which case you can do something similar to the following to carry out your conversion:<\/p>\n
    DECLARE @a VARCHAR(100) =\n'<things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>';\nDECLARE @b XML;\nSET @b = CONVERT(XML, @a);\nSELECT @b;\n\n<\/pre>\n
    All we’re doing here is explicitly converting variable  @a<\/code><\/b>  and assigning it to variable  @b<\/code><\/b>, which has been  configured with the XML<\/code><\/b> data type. The SELECT<\/code><\/b>  statement gives us the following results:<\/p>\n
    <things><thing>abc 123 def<\/thing><thing>ghi 456 jkl<\/thing><\/things><\/pre>\n
    One consequence of converting the data in this way is that we lose the tabs and linefeeds  that help make the XML more readable. In many cases, the way in which the XML is displayed will not be an issue.  However, if you want to preserve those tabs and linefeeds, you need to add the style argument to the  CONVERT<\/code><\/b>  function:<\/p>\n
    DECLARE @a VARCHAR(100) =\n'<things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>';\nDECLARE @b XML;\nSET @b = CONVERT(XML, @a, 1);\nSELECT @b;\n\t<\/pre>\n
    The style argument (1<\/code><\/b>) tells the  database engine to preserve any insignificant white space, such as tabs and linefeeds. Now the  SELECT<\/code><\/b> statement  returns results that look like the following:<\/p>\n
    <things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>\n\t<\/pre>\n
    Given how effectively the CONVERT<\/code><\/b> function  handles this conversion, we might expect it to work the same way when we’re converting  XML<\/code><\/b>  data to  VARCHAR<\/code><\/b> data:<\/p>\n
    DECLARE @a XML =\n'<things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>';\nDECLARE @b  VARCHAR(100);\nSET @b = CONVERT(VARCHAR(100), @a, 1);\nSELECT @b;\n<\/pre>\n
    Unfortunately, adding the style argument doesn’t help and we get the following results:<\/p>\n
    <things><thing>abc 123 def<\/thing><thing>ghi 456 jkl<\/thing><\/things><\/pre>\n
    The problem has to do with variable  @a<\/code><\/b>. The database engine is implicitly converting the string value to  the XML<\/code><\/b> type, which  means that the tabs and linefeeds are not being preserved during that assignment. The way to get around this is to use  the CONVERT<\/code><\/b> function to explicitly cast the string to the  XML<\/code><\/b>  type when assigning the value to the data type:<\/p>\n
    DECLARE @a XML = CONVERT(XML,\n'<things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>', 1);\nDECLARE @b  VARCHAR(100);\nSET @b = CONVERT(VARCHAR(100), @a, 1);\nSELECT @b;\n<\/pre>\n
    Now our results are more in line with what we want:<\/p>\n
    <things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>\n\t<\/pre>\n
    None of this is in itself a big deal, but let’s look at what happens if we convert the XML to  the VARCHAR<\/code><\/b> type  without specifying the style argument when setting the value of  @b<\/code><\/b>:<\/p>\n
    DECLARE @a XML = CONVERT(XML,\n'<things>\n       <thing>abc 123 def<\/thing>\n       <thing>ghi 456 jkl<\/thing>\n<\/things>', 1);\nDECLARE @b  VARCHAR(100);\nSET @b = CONVERT(VARCHAR(100), @a);\nSELECT @b;\n<\/pre>\n
    Suddenly our results look quite different:<\/p>\n
    <things>\n	<thing>abc 123 def<\/thing>\n	<thing>ghi 456 jkl<\/thing>
<\/things>\n\t<\/pre>\n
    SQL Server’s XML parser attempts to store special characters such as tabs and linefeeds in a  way that preserves them throughout various processing operations, including retrieving and converting data. This  process, known as entitization, saves tabs as \t<\/code><\/b> and  linefeeds as   <\/code><\/b>, but tabs and  linefeeds are only two types of special characters that the parser can entitize. <\/p>\n
    In most cases, when you retrieve XML directly, these special characters are automatically  displayed in a readable format. That said, the XML parser is not always consistent in how it treats entitized  characters. Notice in the preceding results that only one linefeed is returned as   <\/code><\/b>. In addition, if the database engine comes across a special  character it doesn’t like, it returns an error, rather than trying to entitize it. For instance, the following example  includes an ampersand (&) in each XML element:<\/p>\n
    DECLARE @a XML = CONVERT(XML,\n'<things>\n       <thing>abc & def<\/thing>\n       <thing>ghi & jkl<\/thing>\n<\/things>', 1);\nDECLARE @b  VARCHAR(100);\nSET @b = CONVERT(VARCHAR(100), @a, 1);\nSELECT @b;\n\t<\/pre>\n
    The code now generates the following error message:<\/p>\n
    Msg 9421, Level 16, State 1, Line 953\nXML parsing: line 2, character 14, illegal name character\n<\/pre>\n
    When possible, you can manually entitize the special characters:<\/p>\n
    DECLARE @a XML = CONVERT(XML,\n'<things>\n       <thing>abc &amp; def<\/thing>\n       <thing>ghi &amp; jkl<\/thing>\n<\/things>', 1);\nDECLARE @b  VARCHAR(100);\nSET @b = CONVERT(VARCHAR(100), @a, 1);\nSELECT @b;\n\n<\/pre>\n
    But even in this case, the CONVERT<\/code><\/b> function’s  style argument doesn’t return the actual ampersand, only the entitized code, giving us the following results:<\/p>\n
    <things>\n       <thing>abc &amp; def<\/thing>\n       <thing>ghi &amp; jkl<\/thing>\n<\/things>\n\t<\/pre>\n
    If we want to see an actual ampersand, we need to replace the entitized character:<\/p>\n
    DECLARE @a XML = CONVERT(XML,\n'<things>\n       <thing>abc &amp; def<\/thing>\n       <thing>ghi &amp; jkl<\/thing>\n<\/things>', 1);\nDECLARE @b  VARCHAR(100);\nSET @b = REPLACE(CONVERT(VARCHAR(100), @a, 1), '&amp;', '&');\nSELECT @b;\n<\/pre>\n
    Now our results are closer to what we expect:<\/p>\n
    <things>\n       <thing>abc & def<\/thing>\n       <thing>ghi & jkl<\/thing>\n<\/things>\n\t<\/pre>\n
    If you’re converting XML data, you have to take into account how the XML parser works in SQL  Server. Otherwise, you can run into problems in unexpected ways. Even an entitized character can be a showstopper.<\/p>\n
    Failure #9: Failing to take portability into account<\/h2>\n
    Some organizations have been working with SQL Server since its humble beginnings and plan to  continue to do so, having no intention now or in the future to port their databases to another system. As long as SQL  Server is out there, that’s where they plan to stay. <\/p>\n
    For other organizations, the future is not quite so clear-cut. Under the right circumstances,  they’d be more than willing to port their databases to another system, providing the penalty for doing so isn’t too  high. <\/p>\n
    If there is any chance you’ll be among those who will one day jump ship, you need to take  into consideration portability when you write your T-SQL code, and an important part of that consideration is how data  is being converted. <\/p>\n
    A good place to start is to quit relying on implicit conversions, such as the one shown in  the following T-SQL:<\/p>\n
    DECLARE @a DATETIME = GETDATE();\nDECLARE @b VARCHAR(25) = NULL;\nSET @b = @a;\nSELECT @b AS VarValue,\n  SQL_VARIANT_PROPERTY(@b, 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(@b, 'maxlength') AS TypeLength;\n\n<\/pre>\n
    The example converts a DATETIME<\/code><\/b> value to VARCHAR<\/code><\/b>,  as shown in the following results:<\/p>\n\n\n\n\n
    \n
    VarValue<\/b><\/p>\n<\/td>\n
    		\n
    ValueType<\/b><\/p>\n<\/td>\n
    		\n
     \t\tTypeLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Mar 13 2015 11:16AM<\/p>\n<\/td>\n
    		\n
    varchar<\/p>\n<\/td>\n
    		\n
    25<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Although SQL Server has no problem converting the data and returning the results we expect,  we cannot be sure another database system will handle the conversion so easily or in the same way. The solution, of  course, is to make all our conversions explicit:<\/p>\n
    DECLARE @a DATETIME = GETDATE();\nDECLARE @b VARCHAR(25) = NULL;\nSET @b = CONVERT(VARCHAR(25), @a, 13);\nSELECT @b AS VarValue,\n  SQL_VARIANT_PROPERTY(@b, 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(@b, 'maxlength') AS TypeLength;\n\n<\/pre>\n
    The  SELECT<\/code><\/b> statement again  returns the results we expect, but helps to avoid performance issues and surprises. However, this leads to another  concern. The  CONVERT<\/code><\/b> function is specific to SQL Server. If we want to make our  code portable, we need to go with the  CAST<\/code><\/b> function:<\/p>\n
    DECLARE @a DATETIME = GETDATE();\nDECLARE @b VARCHAR(25) = NULL;\nSET @b = CAST(@a AS VARCHAR(25));\nSELECT @b AS VarValue,\n  SQL_VARIANT_PROPERTY(@b, 'basetype') AS ValueType,\n  SQL_VARIANT_PROPERTY(@b, 'maxlength') AS TypeLength;\n\t<\/pre>\n
    The  CAST<\/code><\/b> function works just  like the CONVERT<\/code><\/b> function  in this case, except that CAST<\/code><\/b> conforms to  ISO specifications. Any database system that adheres to these standards will be able to handle the conversion without  the code needing to be modified. With  CAST<\/code><\/b>, we lose the style  features available to  CONVERT<\/code><\/b>, but we’re making the code more portable and avoiding  implicit conversions.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    One of the most certain ways of going wrong with any relational database is to get data conversion wrong. Implicit data conversion is a good feature for the expert but  can cause a lot of trouble to the unwary. These boil down to seven ways of  failing to get data conversion right. Rob Sheldon explains and gives sage advice on how to avoid the problems…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,5994,4179,4150,4151,4252],"coauthors":[],"class_list":["post-1974","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-horribly","tag-source-control","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1974","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1974"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1974\/revisions"}],"predecessor-version":[{"id":92520,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1974\/revisions\/92520"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1974"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}