Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
56
votes
5answers
40k views

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-...
53
votes
1answer
23k 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 ...
31
votes
4answers
28k 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 ...
29
votes
4answers
25k 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 ...
26
votes
6answers
46k 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 ...
25
votes
2answers
34k views

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 ...
21
votes
3answers
13k 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 ...
19
votes
3answers
12k views

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 ...
18
votes
4answers
4k views

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 ...
16
votes
3answers
78k 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 ...
16
votes
2answers
11k views

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)[]; * ...
16
votes
1answer
9k views

Why is adding a NOT NULL column with a default constraint instantaneous?

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100)) INSERT INTO TestTab (st) values ('a') INSERT INTO TestTab (st) values ('b') INSERT INTO TestTab (st) values ('c') INSERT INTO TestTab (st)...
14
votes
3answers
45k 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 DataValue column. If I try to run the following command: ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 ...
14
votes
1answer
35k views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now the ...
14
votes
3answers
14k 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 ...
13
votes
3answers
28k views

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 ...
13
votes
2answers
3k views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
12
votes
4answers
403 views

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260). Using ALTER TABLE [...
12
votes
5answers
40k 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 ...
12
votes
3answers
663 views

Increase speed altering column on large table to NON NULL

I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
12
votes
2answers
4k views

Add NOT NULL constraint to large table without table scan [duplicate]

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 ...
12
votes
2answers
10k 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 ...
12
votes
4answers
9k views

Dropping a constraint (index) on a column

How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
11
votes
3answers
32k views

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 ...
11
votes
2answers
14k 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 ...
11
votes
1answer
1k views

Is ALTER TABLE … DROP COLUMN really a metadata only operation?

I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation. Source How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
11
votes
1answer
3k 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 ...
10
votes
2answers
6k views

Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?

We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to ...
10
votes
1answer
56k views

What happens when you modify (reduce) a column's length?

Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and VARCHAR(...
9
votes
2answers
6k views

Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation? In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
8
votes
3answers
35k views

SQL set allowed values for a column

I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only '...
7
votes
2answers
12k views

alter column size take very long time?

I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly. alter table T add C char(25) Then I found the size is too small so I run the ...
7
votes
2answers
45k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
6
votes
4answers
11k views

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
6
votes
3answers
25k 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 ...
6
votes
3answers
21k 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.
6
votes
3answers
1k views

Does alter column change datatype use tempdb version store?

I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full. As far as I know it only takes your memory pages and log ...
6
votes
1answer
4k views

How to add a PostgreSQL 10 identity column to an existing table?

I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE. Is it also possible to add an identity column to an existing table? For ...
6
votes
1answer
13k views

Dropping multiple KEYS of a table

Dropping multiple KEYS in a table is a preferred option or one by one is preferred? “DROP KEY A,DROP KEY B,DROP KEY C" For more 900 million records in the table. How does it work internally?
6
votes
2answers
5k views

Does ALTER TABLE - ADD COLUMN lock the table?

I have a question about the ALTER TABLE ... ADD COLUMN DDL statement. On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in ...
6
votes
1answer
736 views

How to add a PostgreSQL 10 identity column to an existing table with rows?

I have a table with some rows and I try adding an identity column using the command: ALTER TABLE sourceTable ADD COLUMN ogc_fid int GENERATED BY DEFAULT AS IDENTITY; The ...
6
votes
3answers
21k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
5
votes
5answers
8k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
5
votes
1answer
45k views

Altering a column: null to not null

I'm having to change a column in MYSQL to NULL to NOT NULL, but I get the error excutar the syntax. I want to change only the last column, already performed some research but have not found the ...
5
votes
3answers
2k views

Migrating from NTEXT to NVARCHAR(MAX)

I have table with the columns of type NTEXT. And for some peculiar reasons, the client wants to migrate the column type to NVARCHAR(MAX). While doing so, what all potential threat should I be careful ...
5
votes
3answers
1k views

How to change primary key's data type from int to bigint in a huge transactional database under tremendous load?

This is a legacy system, with more than 1 billion records in a table that undergoes 10 million transactions per day. Now the application complains that: Arithmetic overflow error converting ...
5
votes
5answers
40k views

How To change the column order?

How To change the column order in sql server 2008 R2? Eg: My table order is Eid------Ename-----Esalary Know i want this format like Eid---Salary----Ename Thanks.
5
votes
2answers
22k views

How long will it take to rename table

How long will it take to rename a table with a size of 219 GB? This is the specification of our server: Memory: 65 GB Available disk space: 266 GB MySQL Ver 14.12 Distrib 5.0.77 Sorry for the ...
5
votes
2answers
2k views

Will altering an nvarchar(max) column to nvarchar(50) lock the table?

I have a table in SQL Server (Azure) with 1.4 million rows. I'd like to index one of the columns; however, its datatype is nvarchar(max) (defaulted to such by using EF I imagine). I'm using Entity ...
5
votes
2answers
2k views

Truncating table without using Alter Table permission

So my DBA doesn't want developers to give Alter table permission. Now one of the procedures that we created requires to truncate tables (since apparently delete only removes records, but truncate ...

1
2 3 4 5 6