<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Solomon Rutzky
»
Restructure 100 Million Row (or more) Tables...
34 posts, Page 1 of 4
1
2
3
4
»
»»
Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!
Rate Topic
Display Mode
Topic Options
Author
Message
Solomon Rutzky
Solomon Rutzky
Posted Monday, April 25, 2011 9:34 PM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285,
Visits: 1,377
Comments posted to this topic are about the item
Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!
SQL# -
http://www.SQLsharp.com/
Post #1098390
Luke C
Luke C
Posted Tuesday, April 26, 2011 6:43 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 6:14 AM
Points: 238,
Visits: 900
Solomon, this is a great post and thanks for sharing! Just this last week I took a similar approach with 4 large tables that had multiple columns being changed from varchar to nvarchar to support our localization effort. Much of the techniques you mention here were used with the exception of triggers. Given that we were on SQL Server 2008 I was able to use Change Tracking in place of triggers which resulted in 0 modifications to the existing tables. I'd be interested to hear how other folks have used Change Tracking or Change Data Capture. Thanks again for sharing!
Luke C
MCP, MCTS, MCITP - Database Administrator & Database Developer
Post #1098583
sathishsathish81
sathishsathish81
Posted Tuesday, April 26, 2011 7:21 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, August 03, 2012 6:18 AM
Points: 1,
Visits: 70
If Suppose i have 1 Billion Records in the Table. what is the time complexity. Could you please explain the time complexity.
Post #1098611
jvrakesh-858370
jvrakesh-858370
Posted Tuesday, April 26, 2011 7:36 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, April 09, 2012 9:16 AM
Points: 33,
Visits: 31
This is a very good article . My Client here would do enahancements to the application frequently and I end up adding columns to the table with over 3 million rows. Any suggestions how I would do in this scenario?
Post #1098623
noeld
noeld
Posted Tuesday, April 26, 2011 11:55 AM
SSCertifiable
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260,
Visits: 1,977
The article was well explained and straight to the point.
The title on the other hand is *VERY* misleading (seconds?) Apparently you don't count the time you spent setting AND processing it all ...
* Noel
Post #1098881
Solomon Rutzky
Solomon Rutzky
Posted Tuesday, April 26, 2011 12:11 PM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285,
Visits: 1,377
Luke C (4/26/2011)
Solomon, this is a great post and thanks for sharing! Just this last week I took a similar approach with 4 large tables that had multiple columns being changed from varchar to nvarchar to support our localization effort. Much of the techniques you mention here were used with the exception of triggers. Given that we were on SQL Server 2008 I was able to use Change Tracking in place of triggers which resulted in 0 modifications to the existing tables. I'd be interested to hear how other folks have used Change Tracking or Change Data Capture.
Hey Luke. Thanks! CDC is an interesting option that I did not think of due to us being on SQL Server 2005. But if I get the chance (someday
), I will revise this article with that option.
Take care,
Solomon...
SQL# -
http://www.SQLsharp.com/
Post #1098894
Solomon Rutzky
Solomon Rutzky
Posted Tuesday, April 26, 2011 12:14 PM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285,
Visits: 1,377
sathishsathish81 (4/26/2011)
If Suppose i have 1 Billion Records in the Table. what is the time complexity. Could you please explain the time complexity.
Hello. The amount of time it takes to move the data over varies based on several factors:
1) How wide is each row
2) How much activity/contention is there on the table
3) How fast is the underlying disk subsystem
4) etc?
So it really takes some testing on each system to really have a decent idea.
Take care,
Solomon...
SQL# -
http://www.SQLsharp.com/
Post #1098897
Solomon Rutzky
Solomon Rutzky
Posted Tuesday, April 26, 2011 12:15 PM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285,
Visits: 1,377
jvrakesh-858370 (4/26/2011)
This is a very good article . My Client here would do enahancements to the application frequently and I end up adding columns to the table with over 3 million rows. Any suggestions how I would do in this scenario?
Hello. I don't really understand your question. Can you please explain in more detail? Thanks.
Take care,
Solomon...
SQL# -
http://www.SQLsharp.com/
Post #1098898
Solomon Rutzky
Solomon Rutzky
Posted Tuesday, April 26, 2011 12:22 PM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285,
Visits: 1,377
noeld (4/26/2011)
The article was well explained and straight to the point.
The title on the other hand is *VERY* misleading (seconds?) Apparently you don't count the time you spent setting AND processing it all ...
Hi Noel. I am sorry if you feel it was misleading, but I did address this in my article at the beginning of the Overview section. The main intent of the "quickly" making changes was to minimize customer / system impact. The end result is that from the outside (and from the perspective of the application), the restructuring does indeed take only seconds. I also stated in the article summary that this is a way to make large-scale changes that do not require a down-time or maintenance window.
Take care,
Solomon...
SQL# -
http://www.SQLsharp.com/
Post #1098909
AmolNaik
AmolNaik
Posted Tuesday, April 26, 2011 1:30 PM
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:20 PM
Points: 936,
Visits: 1,184
Excellent stuff and very detailed. Thanks for sharing. I have done something similar in the past but never thought of using triggers, instead compared the data between old and new table quickly using Red-Gate data compare tool.
But I guess using the Change tracking feature makes it even more convenient.
Thanks!
Amol Naik
Post #1098958
« Prev Topic
|
Next Topic »
34 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
