Skip to main content
37 votes
Accepted

CREATE TABLE LIKE another table but with additional columns

You can use the syntax below; CREATE TABLE old_table_name ( id serial, my_data text, primary key (id) ); CREATE TABLE new_table_name ( like old_table_name including all, new_col1 integer, ...
Sahap Asci's user avatar
  • 2,979
30 votes

MATCH FULL vs MATCH SIMPLE in foreign key constraints

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo (...
Evan Carroll's user avatar
  • 63.6k
24 votes

Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

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 ...
Stoleg's user avatar
  • 2,388
22 votes
Accepted

Why use master to create a database?

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,...
Aaron Bertrand's user avatar
17 votes

Why use master to create a database?

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 ...
Biju jose's user avatar
  • 2,088
17 votes
Accepted

Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

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 ...
Paul White's user avatar
  • 86.3k
16 votes

Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

Yes, with PostgreSQL 11 This feature is new and landed in Version 11. ALTER TABLE your_table ADD COLUMN new_column integer NOT NULL DEFAULT 0; The above is one such command that will be ...
Evan Carroll's user avatar
  • 63.6k
13 votes
Accepted

How to use default value of data type as column default?

The default default value for any new table column is the default value of the data type. And the default default value for data types is NULL - which is the case for all basic data types in Postgres. ...
Erwin Brandstetter's user avatar
12 votes
Accepted

Clustered index creation fail on table creation

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 ...
Aaron Bertrand's user avatar
12 votes
Accepted

How to prevent asking for password when creating new database in PostgreSQL 10?

use the PGPASSWORD environment variable, @SET PGPASSWORD=something_secret psql -c "CREATE DATABASE mydb" -U postgres postgres or just use a connection string psql -c "CREATE DATABASE mydb" "user=...
Jasen's user avatar
  • 3,593
12 votes

Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

Is this what you're looking for? BEGIN TRANSACTION; ALTER TABLE foo ADD b varchar; EXEC sp_executesql N'UPDATE foo SET b = CAST( a AS varchar )'; ALTER TABLE foo DROP COLUMN a; COMMIT;
Joe Obbish's user avatar
  • 32.5k
11 votes

Trigger to change Database collation on creation

You would need to use dynamic SQL, and the EVENTDATA() function. USE master GO CREATE TRIGGER trg_DDL_ChangeCOllationDatabase ON ALL SERVER FOR CREATE_DATABASE AS SET NOCOUNT ON; DECLARE @...
Erik Darling's user avatar
  • 41.7k
11 votes

How to safely replace the old table with its cleaned copy?

By default, the error message tells you more. Like: ERROR: cannot drop table tbl because other objects depend on it SQL state: 2BP01 Detail: view v_tbl depends on table tbl Hint: Use DROP ... CASCADE ...
Erwin Brandstetter's user avatar
9 votes

How to determine the collation of a table in PostgreSQL?

In addition to a_horse_with_no_name's answer, if you want the collation for the database you're currently connected to, you can use the following query: SELECT datcollate AS collation FROM ...
Daniel Finch's user avatar
9 votes
Accepted

Prevent DROP COLUMN in PostgreSQL

Event Triggers There are event triggers to control DDLs, trapping for the event sql_drop. Call CREATE EVENT TRIGGER. In the trigger function, check to see if the object being dropped is one of your ...
Abelisto's user avatar
  • 1,559
9 votes
Accepted

SQL Server Concurrent Inserts and Deletes

The UPDLOCK, SERIALIZABLE pattern is all about avoiding incorrect results (including false key violation errors) due to race conditions, when performing a particularly common operation known as an ...
Paul White's user avatar
  • 86.3k
9 votes
Accepted

There is no index in the referenced table where the referenced columns appear as the first column

You need an index on the referencing table on ice_product_manuals (product_id). See: dbfiddle-1 The composite index you have, cannot be used for the FK constraint for a peculiar reason. Normally, two (...
ypercubeᵀᴹ's user avatar
8 votes

How do I drop all constraints from all tables?

I started with the accepted answer and modified the structure to use a while loop rather than to build the full sql statement in dynamic sql. I like this better for several reasons. The query is ...
yourbuddypal's user avatar
8 votes
Accepted

Trigger to change Database collation on creation

You cannot, generally speaking, issue ALTER DATABASE within a Trigger (or any Transaction that has other statements in it). If you attempt to, you will get the following error: Msg 226, Level 16, ...
Solomon Rutzky's user avatar
8 votes

Disable multiple logins in one step

No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL: DECLARE @sql nvarchar(max) = N'', @cmd nvarchar(550) = N'ALTER LOGIN $$$ ...
Aaron Bertrand's user avatar
8 votes
Accepted

How long can this ALTER TABLE statement take?

Nobody can tell you with any degree of certainty or accuracy how long this operation will take on your system. However I suggest the change will likely have less impact than you think. I set up a ...
Aaron Bertrand's user avatar
8 votes

How to check if the SYSTEM_VERSIONING for a table is ON?

You could query sys.tables ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = On ); select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary' | name | temporal_type | ...
Scott Hodgin - Retired's user avatar
8 votes

How to check if the SYSTEM_VERSIONING for a table is ON?

Alternatively, you could check its OBJECTPROPERTY: TableTemporalType. The following code will perform a logical check to see if it is a Temporal Table, and if so, deactivate System Versioning. IF ...
solutionist's user avatar
8 votes
Accepted

What events still occur within a transaction that gets rolled back?

For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be ...
Solomon Rutzky's user avatar
8 votes

Disabling triggers makes DELETE fast, but the table has no triggers?

A side effect of this command is that you take an exclusive lock on the table: ALTER TABLE DISABLE trigger ALL; The manual on the DISABLE TRIGGER clause: This command acquires a SHARE ROW EXCLUSIVE ...
Erwin Brandstetter's user avatar
7 votes
Accepted

If Exists before Update or Delete?

Generally, this pattern is more efficient and far less likely to lead to deadlocks or other concurrency issues: UPDATE dbo.Person SET FavoriteColor = 'Green' WHERE Name = 'Bob' AND COALESCE(...
Aaron Bertrand's user avatar
7 votes

Can I specify a name for the index which SQL Server creates for my unique constraint?

Referencing the documentation for Create Unique Constraints, you should be able to use something like this: CREATE TABLE [dbo].[Something]( [IdentityId] [uniqueidentifier] NULL, CONSTRAINT [...
Scott Hodgin - Retired's user avatar

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