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.
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.
--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 …
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
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.