Column Delete Issue

Question

Column Delete Issue

sqlquest2575

SSCommitted

Points: 1802
More actions
July 1, 2011 at 7:56 am

#240846

Hello Friends
I need to delete a column from a table that has 30000 records and it should not effect the performance now would like to know your expert opinion on getting this task done. Please help me to complete this task.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

30k rows are not that much by just looking at the number itself. It depends on how frequent the table is queried and how many indexes are in place. If there a thousands of requests per second and dozend of indexes, it might influence performance.

It also depends if those rows can be found using an index or if a table scan needs to be performed.

Without knowing more about the system in general and the table specifically, my answer would be: Maybe performance is influenced, maybe not. Or: it depends 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

sqlquest2575 (7/1/2011)
I need to delete a column from a table that has 30000 records and it should not effect the performance now would like to know your expert opinion on getting this task done. Please help me to complete this task.

Sensitive thing to do is...

1- Test on your Test/QA enviroment.

2- Wait until next maintenance window to do it on production.

_____________________________________
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.

rafidheenm SSC Veteran Points: 240 More actions · Answer 3

30000 records will not affect the performance by removing the column. It may affect your performance if you have clustered index on it.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

The good news is that:

#1 30,000 rows is not a lot of data.

#2 Dropping a column is not a change of data operation, meaning that the column is marked as a dropped in the table's metadata and the size of each row is not changed. The column won't be physically removed from the table and space reclaimed until the next clustered index rebuild. So, even if the table is heavily accessed, there may be a momentary (perhaps a couple of seconds at most) schema lock placed on the table.

This article goes into more detail:

Paul Randal: Size-of-data operations when adding and removing columns

http://www.sqlmag.com/sponsoredblog/sql-server-questions-answered-28/sql-server/size-of-data-operations-when-adding-and-removing-columns-137144

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho