Implicit conversion

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

Data comparison

Keep in mind: for comparing two values, they need to be of the same type.

Otherwise:

SQL Server converts from one type to another (IMPLICIT)
The developer explicitly converts the data (EXPLICIT)

Example of possible conversions

SELECT 
    company
    bean_type,
    cocoa_percent
FROM ratings;

Example of possible conversions

SELECT 
    company
    bean_type,
    cocoa_percent
FROM ratings
WHERE cocoa_percent > 0.5;

| company | bean_type  | cocoa_percent |
|---------|------------|---------------|
| Amedei  | Blend      | 0.7000        |
| Bonnat  | Trinitario | 0.7500        |
| ...     | ...        | ...           |

Example of possible conversions

SELECT 
    company
    bean_type,
    cocoa_percent
FROM ratings
WHERE cocoa_percent > -2;

| company | bean_type  | cocoa_percent |
|---------|------------|---------------|
| Amedei  | Blend      | 0.7000        |
| Bonnat  | Trinitario | 0.7500        |
| ...     | ...        | ...           |

Example of possible conversions

SELECT 
    company
    bean_type,
    cocoa_percent
FROM ratings
WHERE cocoa_percent > GETDATE();

| company | bean_type  | cocoa_percent |
|---------|------------|---------------|
| ...     | ...        | ...           |

Example of possible conversions

SELECT 
    company
    bean_type,
    cocoa_percent
FROM ratings
WHERE cocoa_percent > 'A';


| result                                         | 
|------------------------------------------------|
| Error converting data type varchar to numeric. |

Example of possible conversions

SELECT 
    company
    bean_type,
    cocoa_percent
FROM ratings
WHERE cocoa_percent > '0.5';

| company | bean_type  | cocoa_percent |
|---------|------------|---------------|
| Amedei  | Blend      | 0.7000        |
| Bonnat  | Trinitario | 0.7500        |
| ...     | ...        | ...           |

Data type precedence

![Data type precedence, from highest to lowest

Implicit conversion between data types

Implicit conversion table

Performance impact of implicit conversion

Implicit conversion is done for each row of the query
Implicit conversion can be prevented with a good database schema design.

Let's practice!

Functions for Manipulating Data in SQL Server