It very much depends on the details of your requirements.
If you have sufficient free space (at least 110% of pg_size_pretty((pg_total_relation_size(tbl))) on disk and can afford a share lock for some time and an exclusive lock for a very short time, then create a new table including the uuid column using CREATE TABLE AS. Why?
What causes large INSERT to ...
Check the CREATE TABLE page of the manual:
There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE
(which is the default). MATCH FULL will not allow one column of
a multicolumn foreign key to be null unless all foreign key columns
are null; if they are all null, the row is not required to have a
match in the referenced table. ...
TWO TABLES IN THE CURRENT DATABASE
If you want to know if two tables are different, run this
SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
AND table_name IN ('...
You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types.
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
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 ...
To check for non-default collations on columns, you can use the following query:
where collation_name is not null
order by table_schema,
Edit: to find the collation of the database, you need to ...
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 ...
You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair.
Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. ...
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 ...
It is absolutely not a requirement in this very specific case, but it is a requirement in many other scenarios. If you're creating a database called Sales, and you arelady have a database called Sales, you'll need to change your database context before you:
Restore with replace; or,
Drop the current database and then:
Create from scratch; or,
Create for ...
You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See ...
Since you already know which character is hidden, you can easily construct the current name using a variable, pass that into sp_rename, and then drop the table using the newly acquired name:
DECLARE @t NVARCHAR(128) = N'dbo.Table' + NCHAR(31) + N'_Name';
EXEC sys.sp_rename @t, N'NewTableName', N'OBJECT';
DROP TABLE dbo.NewTableName;
(Always be careful ...
You don't need triggers or PL/pgSQL at all.
You don't even need DEFERRABLE constraints.
And you don't need to store any information redundantly.
Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, ...
I don't have a "best" answer, but I have a "least bad" answer that might let you get things done reasonably fast.
My table had 2MM rows and the update performance was chugging when I tried to add a secondary timestamp column that defaulted to the first.
ALTER TABLE mytable ADD new_timestamp TIMESTAMP ;
UPDATE mytable SET new_timestamp = old_timestamp ;
CREATE USER shims FROM LOGIN shims;
ALTER ROLE SqlAgentUserRole ADD MEMBER shims;
Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:
I believe it's not a requirement that you should use the master database to create a database. Since the create database command should be run in a database context the documentation always uses a default database which is master and it's a system database which will be always there no matter what so the script doesn't fail!
Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main exception is Deferred Name Resolution but that applies to tables, not columns:
Deferred name resolution can only be used when you reference nonexistent table ...
The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes.
Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML.
The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are:
TRUNCATE can change storage parameters (the NEXT parameter), and those are part ...
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 ...
You can use the syntax below;
CREATE TABLE old_table_name (
primary key (id)
CREATE TABLE new_table_name (
like old_table_name including all,
The fiddle is here
One sure way to speed up an ALTER TABLE is to remove unnecessary indexes
Here are the initial steps to load a new version of the table
CREATE TABLE s_relations_new LIKE s_relations;
# Drop Duplicate Indexes
ALTER TABLE s_relations_new
DROP INDEX source_persona_index,
DROP INDEX target_persona_index,
DROP INDEX ...
You've forgotten about other types of constraint than primary key (also applies to unique, check, and foreign key constraints) but that's basically it.
A column-level constraint can only reference the column that it is declared next to. A table-level constraint can reference multiple columns.
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).
The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds:
there is only one copy of the object to maintain
the developer doesn't have to provide three- or four-part names
CREATE FUNCTION dbo.LastIndexOf(...) ...
ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though.
Actually reclaiming disk space is the tricky part. You ...
CREATE TRIGGER PreventCursorUDFs
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA();
RAISERROR('Yo, no cursors in functions!', 11, 1);
For many people, the MySQL Achilles' heel is implicit commit.
According to Page 418 Paragraph 3 of the Book
the following commands can and will break a transaction
SET AUTOCOMMIT = 1
When it comes to MySQL,...
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 ...
The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016):
The other instances, ...