Database Table Alteration

In database side, Sometimes we come across a situation, where we have to add new column or alter existing column in the table. We are doing all this thing by using GUI that SQL Server or Oracle/MSSQL provides us. But we should know that what is happening behind the scene when the table have a millions of records, lots of constraints etc.

-> the GUI will do the following:
· build a new table to the correct specification
· migrate the data from the old table to the new.
· drop all the constraints against the original table
· recreate all the constraints against the new table
· drop the original table
· rename the new table to the original name

So GUI does all the grunt work behind the scene. So the suggested way is, we have to use “Query” to accompish that type of task.
For exa.

ALTER TABLE [Table1] ALTER COLUMN [col_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

This will save our time and do not affect the table’s existing constraint.
Hope this info may help u…

Advertisements

About Sanju
I am Software Programmer. I am working in JAVA/J2EE Technologies.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: