\nI 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.\n<\/p>\n
\nCREATE FUNCTION somefunc()
\nRETURNS NVARCHAR(4)
\nAS EXTERNAL NAME MyAssembly.MyClass.MyFunction\n<\/p>\n
\nWhen 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."\n<\/p>\n
\nI agree. Except now its works differently than this T-SQL function:\n<\/p>\n
\nCREATE FUNCTION somefuncT()
\nRETURNS NVARCHAR(4)
\nAS
\nBEGIN
\n RETURN N'Hello World'
\nEND\n<\/p>\n
\nCorrect 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.\n<\/p>\n
\nSo what's your preference, backward compatibility or lack of silent data loss (and exceptions)? Just curious…<\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,38,40],"tags":[],"class_list":["post-939","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sqlclr","category-transact-sql"],"yoast_head":"\n