August 24, 2015 at 5:16 am
Yes I am a noob in SQL Server, realised it today:hehe:
Can any experts share the steps on what to check ,what action do i need to take while adding a new column to a table.
August 24, 2015 at 5:41 am
ALTER TABLE
Books Online -> https://msdn.microsoft.com/en-us/library/ms190273.aspx
August 24, 2015 at 6:19 am
What you include in your ALTER TABLE depends on what you want for the the new column. At the minimum, you'll need:
alter table schema_name.table_name add column column_name data_type;
However, you can add constraints, default values, etc.
The previously-posted Books Online link is the definitive reference.
August 24, 2015 at 6:38 am
Sorry my bad.. Thanks for reply.
What I am looking for is the best practices and steps to consider while adding a column to a Table.
Ex: Consider a table having 50 columns , 10 Indexes including CI, many relationships.
New column is NVARCHAR(255)
In this scenario, is it good to add new column? what all impact this new column will make?
August 24, 2015 at 7:25 am
Gregg Dave (8/24/2015)
Sorry my bad.. Thanks for reply.What I am looking for is the best practices and steps to consider while adding a column to a Table.
Ex: Consider a table having 50 columns , 10 Indexes including CI, many relationships.
New column is NVARCHAR(255)
In this scenario, is it good to add new column? what all impact this new column will make?
There is nowhere near enough information here for anybody to give you a solid answer.
I would start asking questions about normalization when I see tables with 50 columns. That is larger than most normalized tables should have.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 25, 2015 at 4:50 am
I'm with Sean. It sure sounds like normalization might be an issue. I'd focus there as well.
In terms of impact of adding a column, it depends. If you're talking about adding a nullable column, minimal to no impact on the existing structure when you add the column. But, let's say it's a not null column, and you have 11 indexes, foreign keys, etc., you have a much bigger task. To add the not null column, you can't use ALTER TABLE. You have to create a new table and then migrate the data and indexes over to the new table. You'll have to rename the table, migrate the objects in a pre-determined order; primary keys, foreign keys and clustered index first, data second (to take advantage of the checks offered by the constraints), then secondary indexes. Although, depending on the amount of data, you might want to adjust that, possibly, and have just the clustered index first, then the data, and trust that having your constraints on the original table ensures that the data is good on the new table. That can perform faster. Finally, checks in place to ensure that the X number of rows on the original is X number on the new one. Then you drop the old table.
I still think your primary concern should be normalization.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply