SQL Function used to alter an existing table object.

learn more… | top users | synonyms

3
votes
1answer
2k views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
2
votes
2answers
2k views

Will an ALTER TABLE ADD COLUMN on a slave break replication?

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
2
votes
1answer
711 views

Add columns Query With parameters

Hello all I'm having 1 Question with following query syntax ALTER TABLE table_name ADD column_name column-definition; Question is can we use parameters to column_name field like @column_name? ...
17
votes
3answers
6k views

How can I add a rowversion column to a large table with minimal downtime

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply ALTER TABLE [Tablename] ADD Rowversion [Rowversion] NOT NULL Then the table is unavailable ...
5
votes
1answer
2k views

Preventing replication of alter table command to change engine

I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a ...
1
vote
2answers
343 views

How to recover previous values in Mysql Table?

I have MySQL Innodb table of 14M rows. There was a column called is_disabled which had different values. By mistake I ran the query without the where clause: update url_queue set is_disabled=1 ...
7
votes
2answers
23k views

Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue). My question is: If I try to run the following sql command: ALTER TABLE VisitorData ...
0
votes
1answer
162 views

Columns are added in wrong order

I have a encountered strange behaviour adding columns to an existing table. I run those scripts using osql several instances. Let Table a_table have initially three columns A_col, B_col and C_col. ...
2
votes
3answers
1k views

Changing a column's data type fills the transaction log

Running this code: ALTER TABLE npidata ALTER COLUMN npi varchar(20) Gives this error: Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why ...
4
votes
5answers
18k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
1
vote
1answer
684 views

Can't run Alter Table command due to locks

I'm unable to drop, delete or alter a database in my Local SQL Server instance. I have run sp_Who2 and there don't appear to be any processes using the database apart from a process which gets ...
3
votes
1answer
3k views

Alter table modify column on a huge table containing more than 25 million records

I want to modify a column on a huge table containing more than 25 million records. I am using sybase 12.5.4. I have read through these posts What is the most efficient way to alter a column ...
6
votes
1answer
5k views

Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows: ALTER TABLE `table` MODIFY ...
4
votes
5answers
2k views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
4
votes
3answers
12k views

How to change the datatype of a column from integer to money?

I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error: cannot cast type MyColumn to money I have tried these two statements, but just haven't ...
4
votes
2answers
3k views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
21
votes
3answers
12k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
11
votes
3answers
10k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
2
votes
1answer
1k views

How can I alter the type of a money-column to decimal in PostgreSQL?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type decimal;...