TL;DR; Check database triggers
It turns our this was a very narrow, unique issue - but one that others might experience in a similar setup.
The performance issue was being caused by a database trigger that was called on grant, create, and alter statements, and based on the user, executed additional (sometimes heavy) logic.
These triggers were running extra ...
If you would like to have other tables reference just the title field, then you can add a UNIQUE index.
First, edit the tours table to add a NOT NULL constraint to the title field:
ALTER TABLE `tours` MODIFY `title` VARCHAR(255) NOT NULL;
Now you can create the index:
CREATE UNIQUE INDEX `udx_title` ON `tours` (`title`);
Be sure to confirm that all values ...
From the documentation:
INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.
The table is being rebuilt because the order of the columns is changing. As you said in your ...
If I understand your question correctly, you are not asking how to create a new column with fresh IDENTITY values. You are asking why we cannot issue ALTER TABLE tbl ALTER COLUMN col int IDENTITY on an existing column.
As you say, all it is is effectively a DEFAULT constraint with a counter.
The answer I think is simply that it was never implemented, clearly ...
You can't add an identity to an existing column but you can start using a sequence.
Maybe this can give you an alternative.
Keeping in mind the first and last paragraph of Ronaldo's answer, another thing to note is there's just no mechanism in place to add an IDENTITY to a tabld after the fact. The IDENTITY column has a couple parameters around it such as the seed and increment value which can even both be negative numbers. An IDENTITY column doesn't need to be part of the ...
So what is so special about an Identity column that this can't be
easily added in retrospect?
An identity does not insert values on a table by itself, it depends on an INSERT command and the old rows on your table would require an UPDATE. If you try to UPDATE an identity column, you get an error like this:
Msg 8102, Level 16, State 1, Line 8 Cannot update ...
With a decent DB engine adding a NULLable column with no default to initially populate it is a very quick operation.
Does it have any impact on performance for example, to export the data, recreate the table with all the columns, and import the data again
Even if you don't get the operation "for free" as the column will be NULLable and initially ...
As long as changes (business rules, use cases, laws, design patterns etc) are applied to the outside world, you need a strategy for changing the data model. This involves not only changing the objects them selfs, but also transform the content from the old state of the model to the new. There are a couple of popular frameworks out there: