January 13, 2015 at 12:22 am
Need to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes
any other good solution?
Thanks
January 13, 2015 at 12:37 am
What is the original data type and what is the new data type?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2015 at 12:56 am
changing from nvarchar to int
January 13, 2015 at 1:00 am
I don't immediately see issues with your plan.
Are there any views on the table?
The query plans for the stored procedures will most likely be recompiled on their next execution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2015 at 1:07 am
When I try to drop old column it passes an error of dependent Stored procedure.
I can drop the index and recreate them however I do not want to the SP 's to be dropped.
How can this be overcome? Is there a way to 'ignore' SP dependency?
January 13, 2015 at 1:11 am
khushbu (1/13/2015)
When I try to drop old column it passes an error of dependent Stored procedure.I can drop the index and recreate them however I do not want to the SP 's to be dropped.
How can this be overcome? Is there a way to 'ignore' SP dependency?
Shouldn't your column be renamed before you drop it?
After all, you are dropping column xxx and not the column with the original name.
There is a free plug-in that can do the renaming process for you:
How to rename a column without breaking your SQL database - See more at: http://solutioncenter.apexsql.com/rename-column-without-breaking-sql-database/#sthash.IlwqBmgo.dpuf%5B/url%5D
Why is it an issue to drop the sp? They will be recompiled anyway.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2015 at 1:33 am
even the renaming will not be allowed since SP are dependent.
We are looking at any Third party tool since the code will eventually promote to Production environment where its more secured.
January 13, 2015 at 1:35 am
khushbu (1/13/2015)
even the renaming will not be allowed since SP are dependent.We are looking at any Third party tool since the code will eventually promote to Production environment where its more secured.
The plugin I mentioned allows you to script out the changes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2015 at 4:26 am
I would just script out the dependent objects, drop them and recreate them. That's going to be the safest way to ensure you get it right.
I'm pretty sure that's all that tool does for you.
For a, not-free, version of a tool that will do this and more, you might want to check out Redgate SQL Compare[/url] (disclaimer, I work for Redgate).
"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 13, 2015 at 11:51 am
I'd try instead to just:
(1) just in case, as a backup, copy existing key column(s) and data column to be changed to a keyed backup table
(2) verify that existing data can be successfully converted; if it can't, modify the data as needed
(3) alter the existing column to int
You'd definitely want to recompile all objects referencing that table, although I suspect SQL itself would force that due to the schema change occurring.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2015 at 10:38 am
khushbu (1/13/2015)
When I try to drop old column it passes an error of dependent Stored procedure.I can drop the index and recreate them however I do not want to the SP 's to be dropped.
How can this be overcome? Is there a way to 'ignore' SP dependency?
How about creating the new column and filling it with the converted data, add the appropriate indexes, then convert the old column to a computed column? Your existing code can continue to address the old column but in new code (or as needed) you use the new column.
- Les
January 14, 2015 at 10:08 pm
[/quote]
How about creating the new column and filling it with the converted data, add the appropriate indexes, then convert the old column to a computed column? Your existing code can continue to address the old column but in new code (or as needed) you use the new column.
- Les[/quote]
This would not be helpful since the column with correct datatype is required to be used by all queries.
However, I have solved the problem by unbinding the SP and then binding them again. Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply