Rich
January 19, 2017 8:38 am
I’d just create a new table and have the app dynamically use the latest table.
So if I there is a table Transactions30000 and processes were scanning that…
Switcheroo process creates and populates new table NewTransactions. At end, sp_rename NewTransactions to Transactions30001
Current processes reading Transactions30000 continue to completion unblocked
New processes start using Transactions30001 which from their perspective simply ‘appears’ ready populated
Periodically have a job drop the ‘old’ transaction tables
Best thing, does not need any funky enterprise features, and can be made to work on any SQL platform where you can query the schema!
Loading...
Reply
Kendra Little
January 19, 2017 8:52 am
Great point — and I agree that versioning the tables is preferable to switching whenever you can do it. I’ll put a note in the article that for new code, it’s better to version the tables and avoid the locking issues altogether. When I’ve worked with this before, there’s usually a user table that notes when versions were published and which version is currently active.
For existing systems that are already using sp_rename or alter schema transfer, I’ve typically suggested going to versioned tables as a long term fix. Unfortunately there’s usually so many code changes involved that it’s tough to get it implemented.
One clarification: the switch option doesn’t require Enterprise features, either. Switch has apparently always been available in Standard Edition for a single-partition table, oddly enough. (Just don’t want someone to read this and think they need a different edition.)
Loading...
Reply
Rob
January 19, 2017 3:41 pm
I like this idea!
Another solution I’ve heard of people using to overcome the locking limitation is to use inline table valued functions in front of the tables. You can then just alter the function to switch to the table with fresh data in it.
Loading...
Reply
Kendra Little
January 20, 2017 10:26 am
I do like the idea of versioning the table, but if I have longrunning queries using the TVF, wouldn’t I have the same issue with blocking and a schema modification lock when trying to change the TVF?
Loading...
Reply
Rob
January 20, 2017 2:46 pm
Yes it does have the same schema modification locking problem… I swear I remember reading or having a conversation that using functions that was less blocking than using views or renames for switching out the tables but now I’m thinking I must have dreamt it. In any event the switch statement seems like a much better approach. Thanks for the article!
Loading...
Reply
Kyle
January 20, 2017 11:19 am
Interesting… Couple things, make sure you have the compression set to the same between all three tables, or you get a:
ALTER TABLE SWITCH statement failed. Source and target partitions have different values for the DATA_COMPRESSION option.
It also complained that the table wasn’t partitioned even though it did the switcharoo anyway.
Loading...
Reply
Ricky Lively
January 20, 2017 4:28 pm
Why not use a view and point it to each new version of the table?
Loading...
Reply
Kendra Little
January 21, 2017 8:57 am
Sure, that can work. However, when you alter the view so that it references the new table, you need a schema modification lock on the view. So if long-running queries are using the view, you have just moved the blocking issue from the table to the view.
I am not saying versioning the tables can’t work — it can work. You just have to handle the data access layer to be more clever about picking up the names of the new tables and using them at the right time. The complexity with that is part of why it’s hard to get it implemented for existing systems using the rename technique.
Loading...
Reply
Ben Davis
February 6, 2017 9:44 am
Thanks so much for posting this high quality blog post! I just used this and it works great! The only thing I changed was to remove “PARTITION 1” from both parts of the query as it would give me the warning:
The specified partition 1 for the table ‘database.dbo.tablename’ was ignored in ALTER TABLE SWITCH statement because the table is not partitioned. [SQLSTATE 01000] (Message 4903)
Changing the statement to something that looks like the below alleviated the warning and it still worked as you described.
ALTER TABLE dbo.StagingTable SWITCH TO dbo.ProductionTable;
Thanks again and keep up the awesome work!
Loading...
Reply
Kendra Little
February 6, 2017 10:17 am
Oh, cool! Thanks for this note, I added a comment to the notes under the code sample pointing this out since not everyone scrolls down to the comments.
Loading...
Reply
Nico Africa
September 22, 2017 3:56 am
We started running into issues caused by ALTER SCHEMA TRANSFER a few weeks ago, so we are looking for alternatives. Partition switching looks promising, and this is actually something we were using prior to ALTER SCHEMA TRANSFER. However, we couldn’t use this for tables referenced by foreign keys on other tables without having to drop then re-create the FKs outside of the switch operation, and the latter is costly in terms of time and its impact on performance due to the size of the table.
I wonder if there are workarounds for this? What about using synonyms and switching the tables they point to… Could this have the same issues as modifying views?
Loading...
Reply
Kendra Little
September 22, 2017 9:32 am
I haven’t tested it, but you have to drop and recreate the synonyms to change them, and that should require schema mod locks — so yes, I would think it’d be the same issue.
Loading...
Reply
esther
February 9, 2018 8:16 am
I want to note that you need to handle the primary key naming as well. First create a primary key with a suffix of staging on the staging table and then rename so that the next time around it doesn’t exist.
Loading...
Reply
esther
February 9, 2018 8:18 am
Never mind my last comment. I confused myself about something.
Loading...
Reply
Robin Hughes-Jones
May 1, 2018 12:53 pm
Actually, Esther, if you require that the staging table does not reuse any IDs in the oldProd then you will need to set the seed accordingly, so thanks for bringing this up for consideration.
Loading...
Reply
Craig
May 15, 2018 2:16 pm
How about using Synonyms to switch between views or tables in two databases, one being the active database and the other the inactive database to where data and changes would be performed and then using the synonyms switch the tables/views? You would wrapper the drop/create of the synonym in a transaction so it would wait for an application or user to let go the lock and then the switch drop/create would happen in a split second so the next person/app would grab the new synonym now pointing at the other now active database and table/view. Besides the momentary locks can anyone see anything wrong with this idea? All the users will be doing are reading the data from these databases, only the SSIS package will update/insert and then it will be only to the inactive database. Thanks.
Loading...
Reply
Kendra Little
May 15, 2018 2:37 pm
Check out Nico Africa’s comment and my reply above – this can work, but you can get large blocking chains due the the schema modification locks. There’s no WAIT_AT_LOW_PRIORITY, unfortunately.
Loading...
Reply
Colin
October 10, 2018 1:54 pm
Thank you for the great post.
I have a question about indexing.
We typically don’t index our staging data, but we do index (heavily) the production table. This leaves our production table unavailable for quite some time. What I’d like to do is do all my indexing on the staging data, and do the swap, such that the newly minted production table immediately has all those indexes from staging.
Is this how it works?
(assuming I have the same indexes on both staging and production tables?)
Loading...
Reply
Kendra Little
October 10, 2018 1:58 pm
Glad to help! Yes, that’s how it works. In fact, to do the switch you are required to have matching clustered and nonclustered index on the source and target tables.
Loading...
Reply
Trinadh
October 28, 2018 12:05 am
Hi ,
Could you please suggest me on below query.
Can we rename the Partitioned table in using sp_rename ( like normal table) command or GUI in SQL server 2014 Version ?
sp_rename “db_name.old_table_name”, “new_table_name”
I seen the below article, It is very good.
My views are below. Kindly correct me, If I am thinking in other direction.
Object id is referred by DB engine, and object name is referred by user(here admin or developer).
partition Logic is work on Partition schema and partition function.
So in this scenario partition schema and function is the same and only changing is table name.
Kindly Suggest.
Loading...
Reply
Kendra Little
October 29, 2018 9:22 am
Hi there,
I didn’t write that other article (and I don’t have time to read it and test all their demo code) but you can use sp_rename with a partitioned table. It works fine.
Kendra
Loading...
Reply
James Jiao
March 19, 2019 6:20 pm
1) This could be problematic if the business has naming convention restrictions. Sure you can switch the staging table to the original table.. but what happens to the naming of all the indexes and constraints? Don’t they need to be individually renamed anyway?
2) What about tables that have triggers? Do they need to be disabled beforehand? How does that work?
Loading...
Reply
Kendra Little
March 22, 2019 2:12 pm
Great questions —
1) When you switch in, you’re taking on the metadata of the table you’re switching to. So the names of the constraints of where you switch to “win” (not the names you had on the staging table). So, should be fine from a naming convention perspective. (Going on memory here, feel free to test tho!)
2) I don’t recall that triggers add any complexity, per the docs on partition switching, ” No INSERT, UPDATE, or DELETE triggers or cascading actions are activated by moving table partitions, and it is not required for the source or target tables to have similarly defined triggers to move partitions.” Source
There were some limitations for a while on some columnstore technologies and readable AG secondaries, but my memory is that table partitioning and switching didn’t have those limitations ever in past versions.
Hope this helps!
Loading...
Reply
Changing a column from int to bigint, without downtime - Andy M Mallon - AM²
December 17, 2019 10:11 am
[…] that I’m using sp_rename here. Kendra Little (blog|twitter) has a great post about using SWITCH PARTITION instead of sp_rename. Unfortunately, the data type mismatch on the […]
Loading...
Reply
Roman
January 20, 2021 3:43 pm
Great post. Thank you! The ALTER TABLE SWITCH doesn’t work in SQL 2012 I suppose.
comment history migrated from wordpressRich January 19, 2017 8:38 am I’d just create a new table and have the app dynamically use the latest table.
So if I there is a table Transactions30000 and processes were scanning that…
Switcheroo process creates and populates new table NewTransactions. At end, sp_rename NewTransactions to Transactions30001
Current processes reading Transactions30000 continue to completion unblocked
New processes start using Transactions30001 which from their perspective simply ‘appears’ ready populated
Periodically have a job drop the ‘old’ transaction tables
Best thing, does not need any funky enterprise features, and can be made to work on any SQL platform where you can query the schema!
Loading... Reply Kendra Little January 19, 2017 8:52 am Great point — and I agree that versioning the tables is preferable to switching whenever you can do it. I’ll put a note in the article that for new code, it’s better to version the tables and avoid the locking issues altogether. When I’ve worked with this before, there’s usually a user table that notes when versions were published and which version is currently active.
For existing systems that are already using sp_rename or alter schema transfer, I’ve typically suggested going to versioned tables as a long term fix. Unfortunately there’s usually so many code changes involved that it’s tough to get it implemented.
One clarification: the switch option doesn’t require Enterprise features, either. Switch has apparently always been available in Standard Edition for a single-partition table, oddly enough. (Just don’t want someone to read this and think they need a different edition.)
Loading... Reply Rob January 19, 2017 3:41 pm I like this idea!
Another solution I’ve heard of people using to overcome the locking limitation is to use inline table valued functions in front of the tables. You can then just alter the function to switch to the table with fresh data in it.
Loading... Reply Kendra Little January 20, 2017 10:26 am I do like the idea of versioning the table, but if I have longrunning queries using the TVF, wouldn’t I have the same issue with blocking and a schema modification lock when trying to change the TVF?
Loading... Reply Rob January 20, 2017 2:46 pm Yes it does have the same schema modification locking problem… I swear I remember reading or having a conversation that using functions that was less blocking than using views or renames for switching out the tables but now I’m thinking I must have dreamt it. In any event the switch statement seems like a much better approach. Thanks for the article!
Loading... Reply Kyle January 20, 2017 11:19 am Interesting… Couple things, make sure you have the compression set to the same between all three tables, or you get a:
ALTER TABLE SWITCH statement failed. Source and target partitions have different values for the DATA_COMPRESSION option.
It also complained that the table wasn’t partitioned even though it did the switcharoo anyway.
Loading... Reply Ricky Lively January 20, 2017 4:28 pm Why not use a view and point it to each new version of the table?
Loading... Reply Kendra Little January 21, 2017 8:57 am Sure, that can work. However, when you alter the view so that it references the new table, you need a schema modification lock on the view. So if long-running queries are using the view, you have just moved the blocking issue from the table to the view.
I am not saying versioning the tables can’t work — it can work. You just have to handle the data access layer to be more clever about picking up the names of the new tables and using them at the right time. The complexity with that is part of why it’s hard to get it implemented for existing systems using the rename technique.
Loading... Reply Ben Davis February 6, 2017 9:44 am Thanks so much for posting this high quality blog post! I just used this and it works great! The only thing I changed was to remove “PARTITION 1” from both parts of the query as it would give me the warning:
The specified partition 1 for the table ‘database.dbo.tablename’ was ignored in ALTER TABLE SWITCH statement because the table is not partitioned. [SQLSTATE 01000] (Message 4903)
Changing the statement to something that looks like the below alleviated the warning and it still worked as you described.
ALTER TABLE dbo.StagingTable SWITCH TO dbo.ProductionTable;
Thanks again and keep up the awesome work!
Loading... Reply Kendra Little February 6, 2017 10:17 am Oh, cool! Thanks for this note, I added a comment to the notes under the code sample pointing this out since not everyone scrolls down to the comments.
Loading... Reply SQL Server Partition – petersnotebook February 14, 2017 8:25 am […] https://www.littlekendra.com/2017/01/19/why-you-should-switch-in-staging-tables-instead-of-renaming/ […]
Loading... Reply Nico Africa September 22, 2017 3:56 am We started running into issues caused by ALTER SCHEMA TRANSFER a few weeks ago, so we are looking for alternatives. Partition switching looks promising, and this is actually something we were using prior to ALTER SCHEMA TRANSFER. However, we couldn’t use this for tables referenced by foreign keys on other tables without having to drop then re-create the FKs outside of the switch operation, and the latter is costly in terms of time and its impact on performance due to the size of the table.
I wonder if there are workarounds for this? What about using synonyms and switching the tables they point to… Could this have the same issues as modifying views?
Loading... Reply Kendra Little September 22, 2017 9:32 am I haven’t tested it, but you have to drop and recreate the synonyms to change them, and that should require schema mod locks — so yes, I would think it’d be the same issue.
Loading... Reply esther February 9, 2018 8:16 am I want to note that you need to handle the primary key naming as well. First create a primary key with a suffix of staging on the staging table and then rename so that the next time around it doesn’t exist.
Loading... Reply esther February 9, 2018 8:18 am Never mind my last comment. I confused myself about something.
Loading... Reply Robin Hughes-Jones May 1, 2018 12:53 pm Actually, Esther, if you require that the staging table does not reuse any IDs in the oldProd then you will need to set the seed accordingly, so thanks for bringing this up for consideration.
Loading... Reply Craig May 15, 2018 2:16 pm How about using Synonyms to switch between views or tables in two databases, one being the active database and the other the inactive database to where data and changes would be performed and then using the synonyms switch the tables/views? You would wrapper the drop/create of the synonym in a transaction so it would wait for an application or user to let go the lock and then the switch drop/create would happen in a split second so the next person/app would grab the new synonym now pointing at the other now active database and table/view. Besides the momentary locks can anyone see anything wrong with this idea? All the users will be doing are reading the data from these databases, only the SSIS package will update/insert and then it will be only to the inactive database. Thanks.
Loading... Reply Kendra Little May 15, 2018 2:37 pm Check out Nico Africa’s comment and my reply above – this can work, but you can get large blocking chains due the the schema modification locks. There’s no WAIT_AT_LOW_PRIORITY, unfortunately.
Loading... Reply Colin October 10, 2018 1:54 pm Thank you for the great post.
I have a question about indexing.
We typically don’t index our staging data, but we do index (heavily) the production table. This leaves our production table unavailable for quite some time. What I’d like to do is do all my indexing on the staging data, and do the swap, such that the newly minted production table immediately has all those indexes from staging.
Is this how it works?
(assuming I have the same indexes on both staging and production tables?)
Loading... Reply Kendra Little October 10, 2018 1:58 pm Glad to help! Yes, that’s how it works. In fact, to do the switch you are required to have matching clustered and nonclustered index on the source and target tables.
Loading... Reply Trinadh October 28, 2018 12:05 am Hi ,
Could you please suggest me on below query.
Can we rename the Partitioned table in using sp_rename ( like normal table) command or GUI in SQL server 2014 Version ?
sp_rename “db_name.old_table_name”, “new_table_name” I seen the below article, It is very good.
https://blogs.msdn.microsoft.com/docast/2015/12/15/sql-server-impact-of-renaming-the-partitioned-table-on-data/ But here my query is why can’t we use the above query to rename partitioned table. what is the Technical reason behind it.
My views are below. Kindly correct me, If I am thinking in other direction.
Object id is referred by DB engine, and object name is referred by user(here admin or developer).
partition Logic is work on Partition schema and partition function.
So in this scenario partition schema and function is the same and only changing is table name.
Kindly Suggest.
Loading... Reply Kendra Little October 29, 2018 9:22 am Hi there,
I didn’t write that other article (and I don’t have time to read it and test all their demo code) but you can use sp_rename with a partitioned table. It works fine.
Kendra
Loading...
Reply James Jiao March 19, 2019 6:20 pm 1) This could be problematic if the business has naming convention restrictions. Sure you can switch the staging table to the original table.. but what happens to the naming of all the indexes and constraints? Don’t they need to be individually renamed anyway?
2) What about tables that have triggers? Do they need to be disabled beforehand? How does that work?
Loading...
Reply Kendra Little March 22, 2019 2:12 pm Great questions —
1) When you switch in, you’re taking on the metadata of the table you’re switching to. So the names of the constraints of where you switch to “win” (not the names you had on the staging table). So, should be fine from a naming convention perspective. (Going on memory here, feel free to test tho!)
2) I don’t recall that triggers add any complexity, per the docs on partition switching, ” No INSERT, UPDATE, or DELETE triggers or cascading actions are activated by moving table partitions, and it is not required for the source or target tables to have similarly defined triggers to move partitions.” Source
Loading... Reply Tim Cartwright April 1, 2019 12:18 pm Kendra, I know this post is a couple of years old but hopefully you can answer this question. According to https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191160(v=sql.105) there is this blurb:
Source and target tables must not be replicated. Neither the source table nor the target table can be sources of replication.
Does that mean you cannot use this technique if the table is currently being replicated in an availability group?
Loading... Reply Kendra Little April 1, 2019 3:30 pm I believe it means for transactional replication. Even that can be enabled for transactional though, more on that here: https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-partitioned-tables-and-indexes?view=sql-server-2017#replication-support-for-partition-switching
There were some limitations for a while on some columnstore technologies and readable AG secondaries, but my memory is that table partitioning and switching didn’t have those limitations ever in past versions.
Hope this helps!
Loading... Reply Changing a column from int to bigint, without downtime - Andy M Mallon - AM² December 17, 2019 10:11 am […] that I’m using sp_rename here. Kendra Little (blog|twitter) has a great post about using SWITCH PARTITION instead of sp_rename. Unfortunately, the data type mismatch on the […]
Loading... Reply Roman January 20, 2021 3:43 pm Great post. Thank you! The ALTER TABLE SWITCH doesn’t work in SQL 2012 I suppose.
Loading...