Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [alter-table]

SQL statement used to alter an existing table object.

ALTER TABLE is used to make DDL or structural changes to an existing table object.

387 questions
69
votes
1 answer

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 and swap them when computation is ready. I could…
Ben
  • 887
  • 1
  • 7
  • 9
61
votes
5 answers

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-type Datetimeoffset(0). I then UPDATED this column…
PapillonUK
  • 713
  • 1
  • 5
  • 5
47
votes
5 answers

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 that I'm not sure what will happen if I run VACUUM…
Justin Rhyne
  • 573
  • 1
  • 4
  • 5
35
votes
4 answers

How to add a column with a foreign key constraint to a table that already exists?

I have the following tables, CREATE TABLE users (id int PRIMARY KEY); -- already exists with data CREATE TABLE message (); How do I alter messages table such that, a new column called sender is added to it where sender is a foreign key…
Hassan Baig
  • 2,079
  • 8
  • 31
  • 44
33
votes
2 answers

Database "frozen" on ALTER TABLE

Our production environment just froze* this morning for a while when altering a table, adding a column actually. Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[]; * Login into our system requires a select from that very…
Gonzalo Vasquez
  • 1,059
  • 2
  • 18
  • 33
31
votes
4 answers

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 changes require modifications of the rows in the…
sh-beta
  • 609
  • 1
  • 5
  • 11
29
votes
6 answers

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 backup everything schedule downtime and do then do…
NeuronQ
  • 393
  • 1
  • 4
  • 6
28
votes
3 answers

Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever…
Mark Amery
  • 1,106
  • 3
  • 14
  • 24
27
votes
4 answers

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 for updates for too long. What strategies can I…
Michael J Swart
  • 2,235
  • 5
  • 23
  • 32
26
votes
2 answers

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an INT). Just the nullability. The statement is as…
Randy Minder
  • 2,032
  • 4
  • 22
  • 41
22
votes
3 answers

Modify all tables in a database with a single command

Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database: ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8; My objective is to modify the charset from…
hodl
  • 321
  • 1
  • 2
  • 6
18
votes
3 answers

Memory Optimized Tables - can they really be so difficult to maintain?

I'm investigating the benefits of upgrading from MS SQL 2012 to 2014. One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast. I've found that there are a few limitations on memory…
Shaul Behr
  • 2,963
  • 8
  • 34
  • 42
18
votes
2 answers

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table statement? I created an index on the column hoping…
Volker Hauf
  • 183
  • 1
  • 1
  • 4
18
votes
3 answers

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 them again. Now my id's go from 68 -> 92 and this…
Gijs
  • 333
  • 1
  • 2
  • 7
17
votes
5 answers

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 do it (assuming I could suffer even downtime - the…
Ran
  • 1,573
  • 9
  • 21
  • 35
1
2 3
…
25 26