Why data types matter
They’re just numbers, right? A date’s a date. It’s just string data, who cares? I admit I have posed those questions before and only much later did I understand how complicated it can get. Angela Henry, our guest for today, has heard these same phrases many times before and she chats with us to enlighten us on the issue and ways we can get around it. Perhaps you have heard about terms like SARGable and implicit conversions. This episode seeks to tie these terms to one of the very first decisions you have to make about your data — the data types.
“The biggest peril in using the incorrect data type is that you can actually get incorrect query results.”
“A number can always be converted into character data, but character data cannot always be converted to a number.”
“Some of the things that can really trip you up is if you leave it in that character data type and then you’re trying to do comparisons against that.”
“When [SQL Server] doesn’t have to do any of those conversions, when it just can do the straight COMPARE for the exact same data types, then your queries are going to be SARGable and you’re not going to take that extra performance hit by having to convert one data type or the other.”
Listen to Learn
01:39 Compañero Shout-Outs
04:46 Intro to the guest and topic
06:22 If you use the wrong data type, you’re going to get incorrect query results
11:06 Converting data types – it comes at a cost
13:59 You want your queries to be SARGable
16:52 You’ll find the information about a performance hit in your execution plan
19:02 Avoid the implicit conversion? Change the data types on the tables
22:18 Data types to avoid using when possible
25:46 Do you really need 8000 characters?
28:42 Why not change characters back into integers?
32:19 Fat tables – how to avoid them
36:04 How compression options can change data type decisions
39:45 SQL Family Questions
48:44 Closing Thoughts
Angela is a DBA/BI Developer, living in High Point, NC and loves what she does. She’s worked with all versions of SQL Server & worn all the hats that come with dealing with SQL Server throughout the years: developer, administrator, data architect and BI developer. She has her MSCE: Business Intelligence and is Data Platform MVP. She volunteers with PASS in various capacities, ranging from Program Committee to cleanup at local SQL Saturday events. She is also the chapter leader for the PASS Triad SQL User Group in Greensboro, NC. In her spare time you can probably find her in or at the pool, she’s an avid US Masters Swimmer, Coach and Instructor.
Meet the Hosts
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.