DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Implicit Conversion and Performance

Implicit Conversion and Performance

Automatically changing data types in SQLServer is a terrible idea for performance. Learn how to get around the limitations in this article.

Grant Fritchey user avatar by
Grant Fritchey
·
May. 11, 16 · Tutorial
Like (3)
Save
Tweet
Share
2.34K Views

Join the DZone community and get the full member experience.

Join For Free

letting sql server change data types automatically can seriously impact performance in a negative way. because a calculation has to be run on each column, you can’t get an index seek. instead, you’re forced to use a scan. i can demonstrate this pretty simply. here’s a script that sets up a test table with three columns and three indexes and tosses a couple of rows in:

create table dbo.converttest (
 bigintcolumn bigint not null,
 intcolumn int not null,
 datecolumn varchar(30)
 );

create index bigintindex 
on dbo.converttest 
(bigintcolumn);
create index intindex 
on dbo.converttest 
(intcolumn);
create index dateindex 
on dbo.converttest
(datecolumn);

with nums
 as (select top (1000000)
 row_number() over (order by (select 1
 )) as n
 from master.sys.all_columns ac1
 cross join master.sys.all_columns ac2
 )
 insert into dbo.converttest
 (bigintcolumn,
 intcolumn,
 datecolumn
 )
 select nums.n,
 nums.n,
 dateadd(hour, nums.n, '1/1/1900')
 from nums;

if i run a very simple query like this:

declare @param datetime;

set @param = '3/15/1963 8:00:00';

select ct.datecolumn
from dbo.converttest as ct
where ct.datecolumn = @param;

i get an execution plan that looks like this:

convert_scan

the issue is, while i put dates into the datecolumn, the data type there is varchar, not datetime. passing a datetime value results in an implicit conversion that we can see in the index scan predicate property:

converimplicithurtsperformance

this is a function against a column and that is what results in the scan. if i modify the code as follows:

declare @param varchar(30);

set @param = '3/15/1963 8:00:00';

select ct.datecolumn
from dbo.converttest as ct
where ct.datecolumn = @param;

i get an execution plan that looks like this:

convert_seek

no change in data type is needed, so the index can be used. it’s a very simple demonstration of how implicit conversions hurt performance. now, what about the integer columns? what happens when i do this with the code:

declare @param int;

set @param = 650323;

select ct.bigintcolumn
from dbo.converttest as ct
where ct.bigintcolumn = @param;

here’s the execution plan:

convert_intseek

and here is the predicate:

convert_predicate

in this case, we’re still getting an implicit conversion of the data type, but sql server is smart enough to realize that, hey, it’s still just an integer, so it can convert the @param instead of the column, resulting in a seek. if i change the test so that it runs a query against the int column passing a bigint value, it doesn’t even do a conversion of any kind.

in short, not all implicit conversions are a problem. however, it’s still a best practice to use the right data type for columns. you should also use the same data type for your parameters and local variables as the column data type. taking these steps avoids issues with implicit conversion.

for lots more information on query tuning, in august, i’ll be doing an all day pre-con at sqlserver geeks annual summit in bangalore india.

Data Types sql Database

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Distributed SQL: An Alternative to Database Sharding
  • Understanding gRPC Concepts, Use Cases, and Best Practices
  • Using the PostgreSQL Pager With MariaDB Xpand
  • Web Application Architecture: The Latest Guide

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: