A growing number of businesses are relying on big data technology to improve productivity and address some of their most pressing challenges. Global companies are projected to spend over $297 billion on big data by 2030. Data technology has proven to be remarkably helpful for many businesses.
However, companies also encounter a number of challenges as they try to leverage the benefits of big data. One of their biggest frustrations is trying to manage their IT resources to store data effectively.
One of the biggest challenges they face is managing their SQL servers. This entails knowing how to use their cast functions properly. Keep reading to learn more.
Problem Statement
When dealing with Structured Query Language (SQL) and programming in general knowing the data types available to you in a given framework is pivotal to being efficient at your job.
Using the wrong data types for your tables can cause issues in the downstream applications which connect to the database, other databases joining to your data and Extract Transform Load (ETL) packages that extract data out.
In this post we will investigate a key function to help with the complexity that is presented with all these data types. In SQL Server this comes in the form of the CAST command. Cast allows you to change data type X to data type Y with varying restrictions. Some data types are unable to be cast to others and there are implicit data conversions and potential precision loss effects to be mindful of.
Syntax
--THE EXPRESSION FOR THE CAST OPERATOR WILL BE THE FIELD OR VALUE BEING FOLLOWED BY “AS” AND THE TARGET DATA TYPE.
SELECT CAST(EXPRESSION)
--FOR A SINGLE VALUE
SELECT CAST(1 AS VARCHAR(1))
--FOR A FIELD
SELECT CAST(MYFIELD AS FLOAT) FROM …
Data Prep
Let us first build a temporary table with some example data and data types.
--CREATE AN EXAMPLE TABLE TO TEST CAST EXPRESSIONS
IF OBJECT_ID(N'tempdb..#CASTEXAMPLE') IS NOT NULL DROP TABLE #CASTEXAMPLE
CREATE TABLE #CASTEXAMPLE
(
ID INT IDENTITY(1,1),
XFLOAT FLOAT,
XVARCHAR VARCHAR(10),
XBIT BIT,
XXML XML,
XDATETIME DATETIME,
XDECIMAL DECIMAL(5,2),
XNUMERIC NUMERIC
)
--INSERT 1 ROW OF DATA
INSERT INTO #CASTEXAMPLE
(XFLOAT,XVARCHAR,XBIT,XXML,XDATETIME,XDECIMAL,XNUMERIC)
SELECT 3.14,'PIE',1,'<?xml version="1.0"?><Root><Location LocationID="1"><CityState>Salem, Alabama</CityState></Location></Root>',GETDATE(), 100,50.01
--VIEW THE TABLE DATA
SELECT * FROM #CASTEXAMPLE
--CREATE ANOTHER EXAMPLE TABLE
CREATE TABLE #CASTJOIN
(
ID INT IDENTITY(1,1),
YVARCHAR VARCHAR(10),
YFLOAT FLOAT
)
INSERT INTO #CASTJOIN
(YVARCHAR,YFLOAT)
SELECT '3.14',3.14
SELECT * FROM #CASTJOIN
Examples
Now that we have some data loaded, let’s take a look at some examples.
First lets look at the concept of explicit casting, explicit casting means that you will have to use CAST() to change the data type.
–EXPLICTLY CONVERT THE XFLOAT FIELD TO BINARY
SELECT CAST(XDECIMAL AS VARBINARY) as VarBinaryResult FROM #CASTEXAMPLE
In this example the decimal value 100.00 is converted to its binary value.
--EXPLICTLY CONVERT THE XFLOAT FIELD TO BINARY
SELECT CAST(XDECIMAL AS VARBINARY) as VarBinaryResult FROM #CASTEXAMPLE
This example fails, because in the #CASTEXAMPLE table, the value is a string ‘PIE’ which cannot be represented as an integer data type. However, if the VARCHAR value happens to be a valid integer value the cast will be successful.
--EXPLICTLY CAST THE XVARCHAR FIELD TO INT
SELECT CAST(XVARCHAR AS INT) as IntResult FROM #CASTEXAMPLE
But how can we be sure that this value is indeed an integer?
To verify our cast was successful we can utilize the sp_describe_first_result_set stored procedure which accepts a tsql string argument. For this procedure we will need double ticks in our string literal `1` in our query string.
--CHECK THE RESULT DATATYPE WITH sp_describe_first_result_set
sp_describe_first_result_set @tsql = N'SELECT CAST(''1'' AS INT) as IntResult FROM #CASTEXAMPLE' 
We can also validate the original data types of the temporary table we created.
--CHECK THE #CASTEXAMPLE TABLE
sp_describe_first_result_set @tsql = N'SELECT * FROM #CASTEXAMPLE'
The contrast to explicit casting is implicit casting which means that this conversion is taken care of for you automatically by SQL Server.
An example of when implicit joining comes into effect is if you are joining on a table with different data types. In this situation SQL server will recognize the data types need to be converted and do the conversion for you. This can add some compute cost to your query, so when dealing with one-to-many relationships it’s good to keep this in mind.
--IMPLICITY CAST ON JOIN
SELECT CE.XFLOAT,CJ.YFLOAT
FROM #CASTEXAMPLE AS CE
JOIN #CASTJOIN AS CJ
ON CE.XFLOAT = CJ.YVARCHAR
--PRECIOUS LOSS
When using the CAST() function you should also be aware of the potential of lost precision. This occurs when converting decimal to numeric or numeric to decimal data types in SQL Server.
Use the Cast Function Properly
In this post we discussed the syntax and use case for the SQL Server CAST() function. We covered the implicit and explicit cast functionality and the performance impacts that different data types can have when joining tables. Also we covered how to check a query results of a TSQL query to validate data types from the CAST() function. This is an important part of SQL database management and monitoring.
Cast is a very widely used function in SQL Server, database objects such as views and stored procedures can also use the CAST() function so understanding the functionality and how to use the function efficiency can save you time and effort! Be sure to experiment with different implementations of CAST() and make note of what works well for your specific environment.
For a better experience, try SQL autocomplete to assist with the various data casting options.
