January 8, 2019 at 5:47 am
Summary:
I have a DB that has combined keys as proimary keys and I have a task to rename one of the columns. What is the best way to do it witout having a down time (or with only some seconds of downtime)?
Situation:
There are 10 different tables having the same type of primary keys: a combined key consist of key1 and key2 (example names, both varchar and it is a clustered index). I have a webserver on top of it and because of some technicalities I have to rename key1 to newKey1 and of cource it is prefered not to have any down times in the server.
I have the code in Database project and if I rename the field in code it will generate the script below that it will run on publishing:
EXECUTE sp_rename @objname = N'[dbo].[table1].[key1]', @newname = N'newkey1', @objtype = N'COLUMN';
GO
-- Refactoring step to update target server with deployed transaction logs
IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
BEGIN
CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
END
GO
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'c27a5d55-e520-4e61-9586-3ca3d6e3dd57')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('c27a5d55-e520-4e61-9586-3ca3d6e3dd57')
Is it a good idea to update the DB by just spinning up the DB to an expensive plan (like 2000 eDTUs) and then just publish the code? Will it need to update all the clustered indexes? Anything important to check/remember before doing this?
Alternatively, I can do it in multiple steps:
1- Manually adding a new column newkey1 and have the default value set to key1
2- Manually changing the primary key to use the new key
3- Upload the code that doesn't have the old key and uses the new key
Is it better? does it have any risks compared to the 1st way? Which one is better and what to consider?
Best Regards,
Ashkan
January 8, 2019 at 8:04 am
Since it's a primary key on the table, you are really limited in choices. I'd would experiment on the side with every one you outlined. Testing is going to be the key here. You have to drop the existing primary key (and associated foreign keys), no matter what else you do. You can't rename the column and have the key in place. Also, you have to sweat the code. Renaming the column is going to affect every bit of code that uses that name. So, usually, you'd wrap all this in a transaction, the rename and the update on the code. Every bit of that is going to lead to blocking and locking, negatively impacting performance. There is no one right way to get this done. So, test alternatives until you have one that works best in your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2019 at 8:09 am
Is it not possible to use a view to provide a layer with different names?
January 8, 2019 at 8:20 am
Sure. Still, lots of coordination will be involved in putting the view in place over the table so that the code isn't affected, renaming the table underneath that (because you can't have a view and a table with the same name), which means dropping foreign keys, then adding the other column, then dropping and recreating the primary key, then changing the view to reflect the new structure...
I'm still where I was. Figure out one or two methods for doing this (as you already have) and then test them. None of these methods will be cost free. So, we're going for least cost.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2019 at 8:29 am
I have to ask.... just what is the supposed technical difficulty on the web server side that they're having with column names? It's important because it will dictate your path. And since these are all Clustered Index PKs, there's a shedload of stuff to consider such as FKs and other things. I also think the people that told you of the problem might be full of hooie and possibly not have a clue as to what the word "abstraction" means.
I agree that that fastest, easiest, and least expensive way (as Andy Robertson suggested) would be rename the underlying tables and put updateable views in their place. Of course, as with anything else, there can be problems with that but they seem trivial to non-existant to me from here.
If they used Hungarian Notation (tbl_) for the tables, people should get a real kick out of having views with a "tbl_" prefix. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2019 at 4:57 am
Grant Fritchey - Tuesday, January 8, 2019 8:04 AMSince it's a primary key on the table, you are really limited in choices. I'd would experiment on the side with every one you outlined. Testing is going to be the key here. You have to drop the existing primary key (and associated foreign keys), no matter what else you do. You can't rename the column and have the key in place. Also, you have to sweat the code. Renaming the column is going to affect every bit of code that uses that name. So, usually, you'd wrap all this in a transaction, the rename and the update on the code. Every bit of that is going to lead to blocking and locking, negatively impacting performance. There is no one right way to get this done. So, test alternatives until you have one that works best in your situation.
Thanks, I will try to test them seperately on a testDB. 🙂
Anything special that you think I should have in mind/ be aware of?
Best Regards,
Ashkan
January 9, 2019 at 4:59 am
allinadazework - Tuesday, January 8, 2019 8:09 AMIs it not possible to use a view to provide a layer with different names?
We need to change the names because the bad naming is making a lot of confusion and we should have updated the column names long before the DB become big.
Still better to fix it now than later. 🙂
Best Regards,
Ashkan
January 9, 2019 at 5:30 am
ashkan sirous - Wednesday, January 9, 2019 4:57 AMThanks, I will try to test them seperately on a testDB. 🙂
Anything special that you think I should have in mind/ be aware of?
Not as such. It's about taking the path that offers the least amount of blocking. In general, I'd suggest doing it the in the most simple and straight-forward way. Multiple steps may sound appealing if you can defer the moment of dropping and recreating that constraint, but, you have to do that no matter what.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2019 at 6:09 am
ashkan sirous - Wednesday, January 9, 2019 4:59 AMallinadazework - Tuesday, January 8, 2019 8:09 AMIs it not possible to use a view to provide a layer with different names?We need to change the names because the bad naming is making a lot of confusion and we should have updated the column names long before the DB become big.
Still better to fix it now than later. 🙂
Ah. Got it.
If you use sp_Rename to rename a column, you WILL need to find all references to that column in all code (Scripts (especially the ones contained in jobs), Procedures, Views, Functions, Triggers, FKs, etc) not only in T-SQL, but also any code in the front-end and other applications that make reference to the column. Of course, that's a part of the reason to catch these types of things early and possibly avoid the problem with proper naming during design time (which you already know but had to say it out loud for folks that may not).
That, notwithstanding, sp_Rename to rename a column doesn't entail movement of data and so is much quicker than (if I'm reading your original post correctly) copying data from one table to a new table.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2019 at 3:36 am
Confucius was asked what he would do if he was a governor. He said he would "rectify the names" to make words correspond to reality!
You're clearly on the right path!
Getting names right from the beginning takes a great insight and forethought!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply