DBA > Articles

Data Conversion in SQL Server

By: Robert Sheldon
To read more DBA articles, visit http://dba.fyicenter.com/article/

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;
SELECT
(@Val1 + @Val2) AS Total,
CASE
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:

Total
IsNumber
5
Yes
If I were to change the second line to... DECLARE @Val2 Char(6) = 'please';
...then I’d get the result...
Total
IsNumber
2 please
No
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
SELECT
@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:
Value3
Value4
abcd
ab
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.

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/