How to Work with data types
Columns and variables used in Transact-SQL each have a data type. The behavior of values in expressions depends on the data type of the column or variable being referenced. For example, as you saw previously, you can use the + operator to concatenate two string values, or to add two numeric values.
The following table shows common data types supported in a SQL Server database.
Exact Numeric
Approximate Numeric
Character
Date/Time
Binary
Other
tinyint
float
char
date
binary
cursor
smallint
real
varchar
time
varbinary
hierarchyid
int
text
datetime
image
sql_variant
bigint
nchar
datetime2
table
bit
nvarchar
smalldatetime
timestamp
decimal/numeric
ntext
datetimeoffset
uniqueidentifier
numeric
xml
money
geography
smallmoney
geometry
Note
For more details on the different data types and their attributes, visit the Transact-SQL reference documentation.
Data type conversion
Compatible data type values can be implicitly converted as required. For example, suppose you can use the + operator to add an integer number to a decimal number, or to concatenate a fixed-length char value and a variable length varchar value. However, in some cases you may need to explicitly convert values from one data type to another – for example, trying to use + to concatenate a varchar value and a decimal value will result in an error, unless you first convert the numeric value to a compatible string data type.
Note
Implicit and explicit conversions apply to certain data types, and some conversions aren’t possible. For more information, use the chart in the Transact-SQL reference documentation.
T-SQL includes functions to help you explicitly convert between data types
CAST and TRY_CAST
The CAST function converts a value to a specified data type if the value is compatible with the target data type. An error will be returned if incompatible.
For example, the following query uses CAST to convert the integer values in the ProductID column to varchar values (with a maximum of 4 characters) in order to concatenate them with another character-based value:
SQLCopy
SELECT CAST(ProductID AS varchar(4)) + ': ' + Name AS ProductName
FROM Production.Product;
Possible result from this query might look something like this:
ProductName
680: HL Road Frame – Black, 58
706: HL Road Frame – Red, 58
707: Sport-100 Helmet, Red
708: Sport-100 Helmet, Black
…
However, let’s suppose the Size column in the Production.Product table is a nvarchar (variable length, Unicode text data) column that contains some numeric sizes (like 58) and some text-based sizes (like “S”, “M”, or “L”). The following query tries to convert values from this column to an integer data type:
SQLCopy
SELECT CAST(Size AS integer) As NumericSize
FROM Production.Product;
This query results in the following error message:
Error: Conversion failed when converting the nvarchar value ‘M’ to data type int.
Given that at least some of the values in the column are numeric, you might want to convert those values and ignore the others. You can use the TRY_CAST function to convert data types.
SQLCopy
SELECT TRY_CAST(Size AS integer) As NumericSize
FROM Production.Product;
The results this time look might like this:
NumericSize
58
58
NULL
NULL
…
The values that can be converted to a numeric data type are returned as decimal values, and the incompatible values are returned as NULL, which is used to indicate that a value is unknown.
Note
We’ll explore considerations for handling NULL values later in this unit