What’s a truncation exception?

I commonly do a demo when teaching SQL Server 2005 where I write a SQLCLR UDF that's returns the string "Hello World". The define it, sans VS autodeploy, like this.

CREATE FUNCTION somefunc()
RETURNS NVARCHAR(4)
AS EXTERNAL NAME MyAssembly.MyClass.MyFunction

When invoked, it returns "Hell", silently truncating the string the CLR sent it. UNTIL Apr CTP. Now its returns "Truncation Exception". Surprise, surprise… After reporting this as a bug, I was told that "That's the way its supposed to work. To prevent silent data loss."

I agree. Except now its works differently than this T-SQL function:

CREATE FUNCTION somefuncT()
RETURNS NVARCHAR(4)
AS
BEGIN
  RETURN N'Hello World'
END

Correct again, it does work differently. The idea is the T-SQL one still silently truncates data, but the SQLCLR one does "the right thing". The T-SQL one still works the way it does for backward compatibility only. Maybe in the next release they'll work the same.

So what's your preference, backward compatibility or lack of silent data loss (and exceptions)? Just curious…

4 thoughts on “What’s a truncation exception?

  1. ‘Backward compatibility’? Interesting way of trying to hide bugs. And even really old ones, too.

    What is the purpose of ANSI_WARNINGS again? Could it possibly be *data-loss prevention*?

    Matija Lah

  2. I would prefer exceptions over silent data loss at any time. But I would like it to work the old way at db compatibility level 80 and the new way (throw exceptions) at level 90. That way ISV’s have the time to resolve the issues but can still deploy using sql 2005.

  3. Would like can exception to be thrown. The code can use the new BEGIN TRY section to catch the exception, log an error and then resort to the old behavior. This way, the exception can be handled safely without breaking code execution and the parameter length can be corrected over time.

  4. No data loss please.. With CLR being a new features, it does not have the need to be backward comaptibile. Maybe they can have a SET option, that will throw the exception even for TSQL procs. People wanting backward compat can turn it off.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.