Hi All,

How to handle IDENTITY Columns when doing Switch IN Switch OUT Partitions in SQL Server

Hi,

I had an Identity column on my existing table (TABLE_MAIN) which I partitioned. I was not sure that SWITCH In & Out will work for IDENTITY column or not.

I removed IDENTITY property from Main Table (TABLE_MAIN) & Created IDENTITY on IN Table (TABLE_IN) & what I did I found Max value (12345) of Identity column from my main Table & Set INDENTITY (12345,1) to my IN table (TABLE_IN) while creating IN Table (TABLE_IN) for Identity column.

I did Switch & It worked fine.

Make sure not to Truncate IN table (TABLE_IN) else It will re-set Identity value. 

Note: ETL is pointing to table (TABLE_IN) for insertions & once data is loaded & I am switching IN One day Data to my main table (TABLE_MAIN). 

Based on my Hands on, I got below Outcome:

1. If a table is having IDENTITY column & that table needs to partition & implement SWITCH OUT & SWITCH IN then IDENTITY has to define only on T1_IN table & defining IDENTITY on T1 & T1_OUT tables are optional. During SWITCH OUT or SITCH IN, It will not check for IDENTITY properties & will not throw any error if 1st table (Source) is having IDENTITY column & another table (Destination) is not having IDENTITY column.

 If table T1 is having data then Initial value of IDENTITY column has to be MAX value+1 of Identity column from T1 & increment by 1.

Note: We should never truncate the T1_IN table else it will re-set the IDENTITY column value to Initial value (MAX value+1 of Identity column from T1). If really truncate is required then need to truncate both the tables T1 & T1_IN & we need to alter the table T1_IN to set initial value of T1_IN to 1 & increment 1.

No need to set SET IDENTITY_INSERT [Schema].[Table Name] ON before SWITCH OUT & IN & no need to set IDENTITY_INSERT [Schema].[Table Name] OFF after SWITCH OUT & IN.