When working with data in a SQL Server database, you might find that some of that data needs to be converted from one data type to another. For example, suppose you need to compare two numbers, one configured with a numeric data type and one configured with a string data type. To perform the comparison, one of those values will have to be converted to the otherís data type.
SQL Server supports two types of data type conversion: implicit and explicit. Implicit means that the database engine will convert the data type automatically, a process invisible to the user. Explicit means that you must specify how the data type should be converted. If you donít specify how SQL Server should convert the data types to do what you want (explicitly), it will try to guess your intentions (implicitly).
In this article, I introduce you to both types of data type conversions and provide examples that demonstrate how they work, with a few warnings about the sort of thing that can go wrong with implicit conversions. The examples are written against the AdventureWorks2008 sample database on a local instance of SQL Server 2008. But you should have no problem running them against the AdventureWorks database on an instance of SQL Server 2005.
NOTE: Data type conversion can also occur when an outside application retrieves data from a SQL Server database. However, this article is concerned only with data conversion between SQL Server objects.
Implicit Data Conversions
When data needs to be converted from one data type to another, SQL Server can often convert the value automatically (implicitly). For example, suppose you want to add two integers together and theyíre configured with different data types, as in the following example:
DECLARE @Val1 CHAR(2) = '2';
DECLARE @Val2 INT = 3;
(@Val1 + @Val2) AS Total,
WHEN ISNUMERIC(@Val1 + @Val2) = 0 THEN 'No'
WHEN ISNUMERIC(@Val1 + @Val2) = 1 THEN 'Yes'
END AS IsNumber;
In this example, Iíve configured the @Val1 variable with the CHAR data type and assigned a value of 2. Iíve configured the @Val2 variable with the INT data type and assigned a value of 3. When I add these values together, SQL Server automatically converts the @Val1 variable to an INT data type and produces a numeric sum, which I confirm by using the ISNUMERIC function. If the function returns a 0, the sum is not numeric. If the function returns a 1, it is numeric. The following table shows the query results, which indicate that the sum is a numeric value:
If I were to change the second line to...
DECLARE @Val2 Char(6) = 'please';
...then Iíd get the result...
If you mix data types such as INT and VARCHAR, SQL Server will always attempt to convert everything to the one that has the highest precedence. This is why you will sometimes get errors if you try to use implicit conversions to add a number to a string.
SQL Server supports a significant number of implicit data conversions that let you easily compare, combine, or move data. Data can be converted within a particular data type grouping, such as one type of string value to another (for instance, NCHAR to NVARCHAR) or between type groupings, such as a numeric value to a string value (for instance, INT to VARCHAR).
For the most part, SQL Server handles implicit conversions seamlessly, as in the first example above. However, you should be aware whenever the database engine performs implicit conversions because some conversions can lead to unexpected results.
For example, when SQL Server converts a string value of one size to a data type of a smaller size, the database engine truncates the value. In the following set of statements, I declare VARCHAR and CHAR variables and then assign values to them:
DECLARE @Val3 VARCHAR(4);
DECLARE @Val4 CHAR(2);
SET @Val3 = 'abcd';
SET @Val4 = @Val3
@Val3 AS Value3,
@Val4 AS Value4;
As you can see, Iíve configured @Var3 with the VARCHAR(4) data type and @Var4 with the CHAR(2) data type. After I assign the string abcd to @Val3, I then set the value of @Val4 to equal @Val3. Because @Val3 is made up of four characters and @Val4 can handle only two characters, the string is truncated, as the following results show:
If you assign a VARCHAR value to a table that has insufficient space to contain it, using an INSERT or UPDATE statement, you would get a Ďtruncationí error. However, if you assign to a local variable or the parameter to a routine, you donít: the value just gets truncated.