As I was troubleshooting a performance issue, I noticed that there was an implicit conversion (SQL Server automatically converts the data from one data type to another) happening in my join. The join was on a column that was named the same in both tables, but one was datatype INT (integer) and the other was a datatype of VARCHAR(50) (variable character up to 50 places).
While the implicit conversion was happening transparently to our coders and users, it was causing performance impacts to the query. I wanted to change the datatype from VARCHAR(50) to an INT, not only to match the other table, but also because INTs are faster to join on than VARCHARs in the SQL engine.
My first step was to make sure there weren’t any values in the column that would have an issue changing to an int. For this task, I am using TRY_CAST to make my life easier.
SELECT TRY_CAST(SuperHeroId as INT) as Result, SuperHeroId
FROM dbo.Lair
WHERE TRY_CAST(SuperHeroId as INT) IS NULL
AND SuperHeroId IS NOT NULL
The TRY_CAST above is checking to see if I can CAST the value as an INT. If it can’t, it will return a NULL value. My WHERE clause will help me quickly identify the values that are failing which will allow me to fix the data before I change the data type on the column.
Once my query doesn’t return any rows, I am ready to change my datatype, which will remove that implicit conversion and increase the performance of any queries using that join.
The song for this post is Beggin’ by Maneskin.
Andrea Allred presents RoyalSQL 
[…] Andrea Allred has a process: […]
Changing Varchar to INT does not sound like a good idea, even with manual changing everything to INT.
How will a user now insert a MyID123? Will get an error and wont be happy.
Similar like a column Gender/Sex – it cannot be BIT anymore, must be VARCHAR or a lot of people will not be happy.
Great question, ID’s need to be some form of an INT (INT, BIGINT, SMALLINT) to help the SQL Engine function better. Joining on a VARCHAR is going to slow down your joins and make things miserable. Total agree on the BIT for Gender/Sex, I have never been a fan of BITS, and that is why I love INTs. You can use so many of them. I use VARCHARS to describe what my INTs mean, but avoid joining on a VARCHAR when possible. I don’t allow users to insert ID’s that I use for joins, I instead allow users to insert their descriptions of the what ID will mean and I handle the ID behind the scenes. Hope this helps and thanks for sharing!
Yes, instead of BIT use TINYINT e.g. for flags if you want to save space. Big fan of TINYINT.
Yes! Love TINYINT!