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 ...
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 ...
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 ...
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 ...
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
There is no need to drop and recreate the index.
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 ...
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 ...
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 ...
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 ...
You just have to add another step - in fact PostgreSQL is already telling you that: column "sender" referenced in foreign key constraint does not exist.
The FOREIGN KEY (aka parent) column has to already exist in order to make it an FK.
I did the following (from here and the documentation). Note that the parent column has to have a UNIQUE ...
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 ...
No, there is no such command. But what you can do is write a quick query to generate the SQL for you like so:
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 ...
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 ...
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 ...
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 ...
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 (...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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).
@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 ...
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 ...
I'm not sure why everyone is telling you that you have to do this in two steps. In fact, you don't. You tried to add a FOREIGN KEY which assumes, by design, the column is there and throws that error if the column is not there. If you add the COLUMN, you can explicitly make it a FOREIGN KEY on creation with REFERENCES,
ALTER TABLE message
ADD COLUMN sender ...
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 ...