There is a bug in the way SQL Server 2008R2 and 2014 convert certain computed columns. - by Philip Lewis

Status : 

 


1
0
Sign in
to vote
ID 862776 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 4/28/2014 5:15:28 AM
Access Restriction Public

Description

SQL incorrectly converts syntax, or convert syntax returns incorrect result.
This is broken in 2008R2 and 2014

select CONVERT([char](4),replace(CONVERT([varchar](5),CONVERT([time](0),dateadd(second,526753400%(100000),'00:00:00'),0),0),':',''),0) as [UseSelectConvert],
       cast(replace(cast(cast(dateadd("S", 526753400 % 100000, '00:00:00') as time(0)) as varchar(5)),':','') as char(4)) as [UseSelectCast]

The second column is the desired result. The first column is the syntax created (and apparently used) during table creation while declaring a computed column (see code below)
Sign in to post a comment.
Posted by Philip Lewis on 8/11/2014 at 4:30 AM
After reading the reference I see that the option is to use CONVERT for the computed column definition and force 121.

(CONVERT([char](4),replace(CONVERT([varchar](5),CONVERT([time](0),dateadd(second,MySec%(100000),'00:00:00'),121),121),':',''),121)))

It is moments like these that one wishes SQLServer had the ability to update a computed column definition, or alternatively, provide a syntax for insertion of a column at a specific position in the table definition. :( Both of these options are known from at least one other dbms I can name. There are simple reasons for wanting to keep columns in a specific order, despite relational theory ignoring column sequence.
Posted by Philip Lewis on 8/11/2014 at 3:06 AM
Is there a solution/workaround for 2008R2.

(We won't be upgrading to 2012 ever, and 2014 not anytime soon)
Posted by Microsoft on 5/28/2014 at 9:21 AM
Thanks for submitting this feedback.

There was a bug in SQL Server 2008: there used to be a difference between the default for computed columns (0) and other expressions (121). This was fixed in SQL2012: the default is now always 121.
Because this was a behavior change, we fixed it under compat level 110. So, to benefit from the fix, please use compat level 110 or 120.
See also the fourth row in the section "Differences Between Lower Compatibility Levels and Levels 110 and 120" on the following page:
http://msdn.microsoft.com/en-us/library/bb510680.aspx

--
Jos de Bruijn - SQL Server PM