91

Use this one command: RENAME TABLE foo TO foo_old, foo_new To foo; It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.


48

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done ...


36

The command you wish to run does take an ACCESS EXCLUSIVE lock on the table, preventing all other access to that table. But the duration of this lock should be just a few milliseconds, as adding a column like the one you want to add does not require the table to be re-written, it just requires metadata to be updated. Where the problem can come in, and I ...


32

When carrying out the command ALTER COLUMN ... NOT NULL This seems to be implemented as an Add Column, Update, Drop Column operation. A new row is inserted into sys.sysrscols to represent a new column. The status bit for 128 is set indicating the column does not allow NULLs An update is carried out on every row of the table setting the new columnn value ...


29

You can reset the identity value by DBCC CHECKIDENT('tableName', RESEED, 0) So next time you insert into TableName, the identity value inserted will be 1. When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case. Now when you truncate the table, it will reset the ...


28

As alluded to by @Souplex in the comments one possible explanation might be if this column is the first NULL-able column in the non clustered index it participates in. For the following setup CREATE TABLE Foo ( A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY, B CHAR(1) NOT NULL DEFAULT 'B' ) CREATE NONCLUSTERED INDEX ix ...


24

There is no need to drop and recreate the index. Just use ALTER TABLE dbo.production_data ALTER COLUMN serial NVARCHAR(32) NOT NULL; This is a metadata only change. Altering a column from NVARCHAR(16) to NVARCHAR(32) does not affect the storage at all. Going the other way round (from NVARCHAR(32) to NVARCHAR(16)) would give you an error about ...


23

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.system_internals_partition_columns, which prevents all the values from having to be written (until they are ...


22

When you issue an ALTER TABLE in PostgreSQL it will take an ACCESS EXCLUSIVE lock that blocks everything including SELECT. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE, CHECK or FOREIGN KEY constraints need expensive full-table scans to verify, etc. If in doubt, you can generally just try it! All DDL in ...


22

Kin has shown you how you can reset the IDENTITY value, but outside of a development environment when you're really removing all of the data, why do you need to do this? I hope you are not intending to maintain a contiguous sequence of IDENTITY values when you are in production. And I hope you aren't really writing your code to hard-code the IDENTITY ...


19

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though. CAUTION (As noted in the comments by @erny): Note that high CPU load due to I/O operations may be expected. You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of ...


19

What is relatively easy - you just have to add another step. The FOREIGN KEY column has to exist in order to make it an FK. I did the following (from here and the documentation): CREATE TABLE x(t INT PRIMARY KEY); CREATE TABLE y(s INT); ALTER TABLE y ADD COLUMN z INT; ALTER TABLE y ADD CONSTRAINT y_x_fkey FOREIGN KEY (z) REFERENCES x (t) ...


19

The syntax you used is from a SQL Server example, not from MySQL. It would be good to check the MySQL documentation about ALTER TABLE syntax. In MySQL, the ALTER COLUMN subclause can only be used for setting or dropping the default value of the column (SET DEFAULT literal or DROP DEFAULT). You need to use either CHANGE COLUMN (note that column name is ...


18

No, there is no such command. But what you can do is write a quick query to generate the SQL for you like so: USE INFORMATION_SCHEMA; SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET UTF8;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "your_schema_goes_here"; Then you can run the output from this to do what you ...


18

No, in-memory really is this unpolished. If you are familiar with Agile you will know the concept of "minimal shippable product"; in-memory is that. I get the feeling that MS needed a response to SAP's Hana and its ilk. This is what they could get debugged in the timeframe for a 2014 release. As with anything else in-memory has costs and benefits ...


16

There is no such mechanism in PostgreSQL. However, you can still avoid the excessive effects of such a table change. The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction: ALTER TABLE your_table ADD COLUMN new_column integer NOT NULL DEFAULT 0; This statement changes the catalog, then ...


16

Here's a better script. It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints. It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like ...


16

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data: CAST the column as NVARCHAR(260) in all codes that uses it. The ...


15

Is there any way to alter the column data type as a metadata-only operation? I don't think so, this is how the product works right now. There are some really great workarounds to this limitation proposed in Joe's answer. ...results in SQL Server rewriting the entire table (and using 2x table size in log space) I'm going to respond to the two parts of ...


14

alter table ExampleTable add (new_column varchar(20) default 'value1', constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));


14

One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your ...


14

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything. Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work (...


14

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance. It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes: Some operations still work in Offline mode only. See ...


14

There are certain circumstances where dropping a column can be a meta-data-only operation. The column definitions for any given table are not included in each and every page where rows are stored, column definitions are only stored in the database metadata, including sys.sysrowsets, sys.sysrscols, etc. When dropping a column that is not referenced by any ...


13

Renaming a table in MySQL does not require a temporary table. There are two statements that perform an equivalent operation. RENAME TABLE t1 TO t2; ALTER TABLE t1 RENAME TO t2; # as long as no other options to ALTER are also specified Renaming a table acquires a metadata lock on the table which requires that no statements be running against the table, no ...


12

The Oracle Administrators Guide says the following: Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column data type, default value, column constraint, column expression (for virtual columns) and column encryption. You can increase the length of an existing column, or decrease it, if all existing data satisfies ...


12

The correct answer depends on the version of the MySQL engine you're using. If using 5.6+, renames and adding/removing indices are performed online, i.e. without copying all the table's data. Just use ALTER TABLE as usual, it'll be mostly instant for renames and index drops, and reasonably fast for index addition (as fast as reading all the table once). ...


12

@ypercube's answer does manage this partially as a metadata only change. Adding the constraint with NOCHECK means that no rows will need to be read to verify it, and if you are starting from a position where the column does not contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint ...


12

Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the ...


11

Assuming all previously-created columns are fixed-width like char and datetime, adding column C above just added it to the end of the fixed-width section of the record (effectively a meta-only change). However, recasting it as a varchar required it to be moved to the variable-width section of the record, forcing an implicit rebuild of the table. Paul Randal ...


Only top voted, non community-wiki answers of a minimum length are eligible