Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.