May 24, 2021 at 11:13 pm
I'm working with partitioned tables and want to perform a partition switch but am having the issues creating a new partitioned table with the identical computed column that is used for the partition hash as identified in the article at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/515dcc3b-c7d8-47de-a373-733f2a560053/sql-server-2008-alter-table-switch-statement-fails-where-partition-key-defined-as-a-persisted?forum=transactsql -- this article is regarding SQL 2008. I'm running 2016 SP2 CU15.
The resolution indicated in the referenced link is to define the default style parameter, but I haven't been able to figure that out.
The computed column is complex but the original column definition is
(CONVERT([bigint],((datepart(year,[Timestamp])*(1000)+case datepart(month,[TIMESTAMP]) when (1) then (1) when (2) then (1) when (3) then (1) when (4) then (2) when (5) then (2) when (6) then (2) when (7) then (3) when (8) then (3) when (9) then (3) when (10) then (4) when (11) then (4) when (12) then (4) end*(100))+[RDirect]*(10))+ascii(left([session_id],(1)))%(3),0))
When I script it out and recreated I get
(CONVERT([bigint],((datepart(year,[Timestamp])*(1000)+case datepart(month,[TIMESTAMP]) when (1) then (1) when (2) then (1) when (3) then (1) when (4) then (2) when (5) then (2) when (6) then (2) when (7) then (3) when (8) then (3) when (9) then (3) when (10) then (4) when (11) then (4) when (12) then (4) end*(100))+[RDirect]*(10))+ascii(left([session_id],(1)))%(3),0)))
The only differences is the new table gets one extra parentheses which seems to be enough to prevent partition switching. I've bolded the discrepancy. Other than that both tables are in the same filegroups with separate physical files.
When I create the table I use the top chunk of code, but then when I look in sys.computed_columns I have the bottom chunk of code.
My guess is this table was created on SQL 2014 or earlier version and has been upgrade over time. Unfortunately, I'm working where there hasn't been much historical information available to me.
Any thoughts, suggestions or solutions are gratefully appreciated.
May 26, 2021 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply