April 19, 2009 at 11:03 pm
I have a huge table to which i need to add new columns periodically....also i have and array temp() which contains the new set of columns to be added to the table.....each time i need to add columns to it...i usually drop the table and then recreate the same with the new columns....this consumes a lot of time....i would like to know if there is any better alternate method to add the new set of columns to the table...without droping and then recreating...
Thanks in advance...
April 20, 2009 at 2:20 am
jose_anisha (4/19/2009)
I have a huge table to which i need to add new columns periodically....also i have and array temp() which contains the new set of columns to be added to the table.....each time i need to add columns to it...i usually drop the table and then recreate the same with the new columns....this consumes a lot of time....i would like to know if there is any better alternate method to add the new set of columns to the table...without droping and then recreating...Thanks in advance...
i think you better review your database design...
if you frequently update a certain table structure because you need to add additional information (column), i suggest that instead of making new columns, how about making a new table?
you might want to review normalization rules brother..
BR
Hayzer
May 4, 2009 at 5:58 pm
Hi,
It would be a better idea to partition the database table to accommodate future growth in terms of columns. That means column partitioning so that the data rows are stored in a well normalized format.
Appended: My meaning of the above is to follow normalization techniques to accomodate the growth in terms of columns. Please do not get me wrong that the Partitioning can be done only by rows. You can also add column data by using extension tables to the primary table by maitaining a Primary Key.
May 6, 2009 at 3:00 am
Hey What are you talking about?? partioning columns, could you give an example??
Please do not misguide people
Partioning is only applicable for rows
May 6, 2009 at 9:36 am
One quick-n-dirty way around is to put a view in front of it that ties in a table with the additional columns. It's an especially easy way to go if the added columns are sparsely populated. Each time you add a column you'd still need to rebuild the annex table, but it'd potentially be smaller and easier. And you could do the full rebuild every once in a while if you wanted to move some of those columns to the main table.
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
May 6, 2009 at 9:55 am
Jose, to echo something mentioned earlier, you may wish to consider full normalization and remember that it is often better to add another table with a primary key-foreign key relationship then it is to add columns.
If you truly need to add columns, then just use the "alter table" command to add columns. Keep in mind this will add the columns to the tail end of a table, but in a relational database being used as a relational database that simply does not matter.
If you are using sql in a non-relational way (something I personally highly and strongly advise against, but I have known people to do it) and you actually care about column order, then your only practial choice is to drop and recreate the table. You can of course use the gui tools in SSMS to do this for you, but it will be dropping and recreating the table behind the scenes anyway.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
May 6, 2009 at 11:00 am
joseph s (5/6/2009)
Hey What are you talking about?? partioning columns, could you give an example??Please do not misguide people
Partioning is only applicable for rows
Horizontal partition is applicable for rows. Vertical partitioning is applicable for columns.
Here's an example:
create table dbo.Phones (
ID int identity primary key,
Country char(3) not null);
go
create table dbo.PhonesUS (
PhoneID int primary key,
constraint FK_PhoneUSID foreign key (PhoneID) references dbo.Phones(ID),
AreaCode char(3) not null,
Exchange char(3) not null,
Number char(4) not null);
go
create table dbo.PhonesExtensions (
PhoneID int not null references dbo.Phones(ID),
ContactID int not null references dbo.Contacts(ID),
Extension varchar(10) not null);
With something like that, you don't have a lot of wasted columns, and you can hold the data in locally correct formats. This would allow you to have a UK phone number and a US phone number in the same database, with correct formatting enforced, and without a lot of nullable columns. Might need a check of some sort, possibly in a trigger, possible a two-column FK, that would make sure you don't have the same PhoneID in more than one table.
That's vertical partitioning. It's quite common.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2009 at 11:11 am
Just out of curiosity ....
How many columns are in the table now?
How often do you add new columns?
How many columns do you add each time?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 6, 2009 at 11:24 am
jose_anisha (4/19/2009)
I have a huge table to which i need to add new columns periodically....also i have and array temp() which contains the new set of columns to be added to the table.....each time i need to add columns to it...i usually drop the table and then recreate the same with the new columns....this consumes a lot of time....i would like to know if there is any better alternate method to add the new set of columns to the table...without droping and then recreating...
Not disagreeing with anything that others have said, however, If you add your new columns ONLY at the end of the column list, you should NOT have to drop or recreate the table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 11:29 pm
The table currently has 538 columns.....it needs to b updated every week almost .....each time 5- 10 columns needs to b added to the table
May 7, 2009 at 6:38 am
I would guess (based on no knowledge of what you are doing--but I do that all the time:-)) that the earlier suggestion that you take a harder look at normalizing the table is the right one. If the new column names are qualifying the data (e.g by time period, or some such) that qualifying information needs to be in one column, with the values in another (e.g. Units/Dollars). That way the rows can grow and be selected by the qualifier value.
Alternately, a second "details" table should be maintained and "outer joined" as required.
Might the table as it stands now, almost resemble a pivoted table? If so, consider restoring it to its un-pivoted, normalized form.
May 7, 2009 at 6:47 am
rajdvs28 (5/4/2009)It would be a better idea to partition the database table to accommodate future growth in terms of columns. That means column partitioning so that the data rows are stored in a well normalized format.
:w00t: Did you get hold of a Beta copy of SQL Server 10,000 -nicknamed "Romulan"?
Share it man! 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 7, 2009 at 6:53 am
Thanks GSquared, for your explanation. The word normalization would make more sense rather than column partitioning simply put.
A rather new guy to sql programming would go all around searching how to partition columns.
Thanks for your effort to reply, great job!!
May 7, 2009 at 7:06 am
joseph s (5/7/2009)
Thanks GSquared, for your explanation. The word normalization would make more sense rather than column partitioning simply put.A rather new guy to sql programming would go all around searching how to partition columns.
Thanks for your effort to reply, great job!!
There are times when it's not used for normalization, but instead for performance reasons.
I had a table in a database where the first five or six columns were accessed constantly, but there were about ten other columns that only had occassional access. The whole thing was adequately normalized, but I found that splitting it in two vertically, into two tables with a 1-to-1 relationship, made the page size so much better for the high volume columns that performance on the most common queries went WAY up. The join didn't add too much overhead to the occassional queries that needed the extra data from the other columns, so they were still okay.
If that had just been selects, a covering index would have provided the same benefits, but it had a lot of updates going on all the time, and this ended up overall faster because of that.
So it can be normalization, or it can be for other reasons. Sparse columns in SQL 2008 solve a very similar problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2009 at 7:06 am
jose_anisha (5/6/2009)
The table currently has 538 columns.....it needs to b updated every week almost .....each time 5- 10 columns needs to b added to the table
You may soon find out that you cannot more columns.
Seriously consider the suggestion made by others about redesigning.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply