More on Implicit Conversions

Yesterday, a reader posted a question asking me to comment on SQL Server’s algorithm for choosing implicit conversions.  When SQL Server encounters an expression with mismatched types, it has two options.  It can execute the query with an implicit conversion or it can fail with an error.  Before digging into the implicit conversion scenario, let’s…

4

Implicit Conversions

In my last couple of posts, I wrote about how explicit conversions can lead to errors.  In this post, I’m going to take a look at some issues involving implicit conversions.  SQL Server adds implicit conversions whenever you mix columns, variables, and/or parameters with different (but compatible) data types in a single expression.  For example,…

5

Conversion and Arithmetic Errors: Change between SQL Server 2000 and 2005

In this post from last week, I gave an example of a query with a conversion where the optimizer pushes the conversion below a join.  The result is that the conversion may be run on rows that do not join which could lead to avoidable failures.  I ran this query on SQL Server 2005.  After…

2

Conversion and Arithmetic Errors

Let’s take a look at a simple query: CREATE TABLE T1 (A INT, B CHAR(8))INSERT T1 VALUES (0, ‘0’)INSERT T1 VALUES (1, ‘1’)INSERT T1 VALUES (99, ‘Error’) SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1 There is no way to convert the string “Error” into an integer, so it should come as no surprise that…

4