Quick Warning about Variables and Truncation
Published: Sep 25, 2016
Well, after a short break let’s start the new year with a nice and short post.

This one is simply because I was reviewing some code for someone the other day as they had an issue they couldn’t track down and, as it turned out, it was something that I’ve definitely been stung by in the past so I thought I’d make a note here.

Basically the problem they were having was that they were using a cursor to update a table and some of the records weren’t updating as expected because they appeared to be truncating somewhere. Not easy to track down in a 12,000 line proc with multiple cursors.

Sadly, as mentioned above, this is a common occurrence which I’ve fallen for myself and it can cause massive problems.

It’s all down to the fact that variables can simply truncate data you pass to them in order to fit their data type without giving you any warning at all.

The behaviour we expect is actually that of a table:

use AdventureWorks2012
go

if object_id('dataTypeTable') is not null
  
drop table dataTypeTable

create table dataTypeTable
(
  
salesOrderID int,
  
puchaseOrderNumber varchar(1)
)
insert into dataTypeTable
select top 1 salesOrderID, PurchaseOrderNumber
from sales.SalesOrderHeader


Quite rightly we get the following error message:

Therefore we find ourselves getting lazy and presuming that SQL will inform us every time it comes across a truncation, but sadly it likes to wait until we’re not looking and then bite us in the rear.

declare @code varchar(1)
select @code =
(
  
select top 1 PurchaseOrderNumber
  
from sales.SalesOrderHeader
)

select @code


As you can see… no error… in fact it reports complete success despite truncating our data and therefore leaving us with a potential issue further down the line. The difference between declaring a varchar as 24 or 25 characters could mean the difference between correct results and expensive, and unseen, mistakes.

In the case I was looking at the variable declaration was made, then the variable loaded as part of a cursor, and the definition was defined as a varchar(50) when, for just 2 records, it needed to be a varchar(55) and this was causing all manner of problems because the first 50 characters of the record matched a join condition and therefore the output was simply wrong but with no immediate indication of where a problem could have occurred or why.

Therefore this post is just a quick note to remind you to always check your data definitions on variables because SQL won’t… it will simply truncate as appropriate.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron