SQL Changes Definition of Computed Column?

• Bryan Lundberg

  SSC Enthusiast

  Points: 150

  More actions

  August 28, 2015 at 12:43 pm

  #320146

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4339.0 (X64)

  So I'm trying to do some partition switching. For this, I'm dynamically generating a copy of the source table using the system tables.

  The switch bombs out with the following error:

  ALTER TABLE SWITCH statement failed. Computed column 'HistoryDate' defined as '(CONVERT([date],dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)),0))' in table 'dbo.History' is different from the same column in table 'tmp.History' defined as '(CONVERT([date],dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)),(0)))'.

  As stated above, my source table has a computed column definition in sys.computed_columns with this value:

  (CONVERT([date],dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)),0))

  When I try to recreate that table with that definition, the system definition of the column changes.

  --Create with expected definition.

  CREATE TABLE tmp.History

  (historydatetime DATETIME,

  historydate AS (CONVERT([DATE],DATEADD(DAY,DATEDIFF(DAY,(0),[HistoryDatetime]),(0)),0)) PERSISTED)

  --Query sys.computed_columns:

  (CONVERT([date],dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)),(0)))

  The final zero is wrapped in parenthesis. That seems to be enough to choke up the partition switching.

  Oddly enough, when I try to create a table with the zero in parenthesis as defined above, the definition is not altered from the create script.

  CREATE TABLE tmp.History2

  (historydatetime DATETIME,

  historydate AS (CONVERT([DATE],DATEADD(DAY,DATEDIFF(DAY,(0),[HistoryDatetime]),(0)),(0))) PERSISTED)

  --Query sys.computed_columns:

  (CONVERT([date],dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)),(0)))

  What's really fun is that the original create script went like this:

  CREATE TABLE wip.History

  (historydatetime DATETIME,

  historydate AS (CAST(dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)) as date)) PERSISTED)

  So, neither is what originally went in.

  Just to check, the above code does result in a value in sys.computed_columns matching my original table.

  (CONVERT([date],dateadd(day,datediff(day,(0),[HistoryDatetime]),(0)),0))

  Is the above expected behavior? Is there a way to reliably reproduce the original script from system tables?

  I could drop and recreate the column in the original source table with the additional parenthesis, but it's large and high-traffic. :/

  Thanks,

  Bryan