SQL Function used to alter an existing table object.

learn more… | top users | synonyms

4
votes
1answer
3k 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 ...
5
votes
1answer
4k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
6
votes
3answers
10k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
7
votes
3answers
7k 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 ...
20
votes
3answers
6k 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 ...
7
votes
1answer
113 views

Adding nullable column to table costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time. ALTER TABLE mytable ...
3
votes
3answers
10k 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 ...
11
votes
5answers
10k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
15
votes
1answer
4k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
4
votes
2answers
2k 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 ...
5
votes
3answers
5k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
5
votes
2answers
13k 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 ...
3
votes
3answers
2k views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
1
vote
2answers
126 views

2013. Lost connection to MySQL server during query error

I have a table with close to 20 million rows. In phpMyAdmin, I attempt to add a new column: ALTER TABLE `myTable` ADD `newColumn` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0'; After about 60 secs ...
1
vote
4answers
120 views

Amount of data in a database table

I am trying to run an alter query on a table, and its giving me a Lost connection to MySQL server during query MySQL server has gone away. The query that I am running is alter table table_name ...
0
votes
3answers
66 views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...