3 ms·
MS SQL Server and Oracle use CREATE SCHEMA and ALTER SCHEMA for DDL transactions. I think it is the ANSI/ISO standard way to do it. But, also "The CREATE SCHEMA
by briansmith 16y ago
MS SQL Server and Oracle use CREATE SCHEMA and ALTER SCHEMA for DDL transactions. I think it is the ANSI/ISO standard way to do it. But, also "The CREATE SCHEMA command does not support Oracle extensions to the ANSI CREATE TABLE and CREATE VIEW commands (for example, the STORAGE clause)."
- mmt 16y agoI'm certainly no expert on standard SQL, but I suspect that's academic. Since my knowledge of transactional DDL is PostgreSQL[1], which supports effectively everything, with no onerous locking, I now have the question: what's the difference? Was my DBA friend in error, or merely out of date? [1] first-hand knowledge, anyway. My 3rd-hand knowledge of MSSQL is that it's more or less Sybase, which does fully support DDL inside a transaction.
- pradocchia 16y agoYes, MSSQL does support transactional DDL, via locking rather than versioning. Not sure how PostresSQL implements it. Anyone? Row versioning would be more concurrency-friendly, and arguably more "right". I'd be interested if anyone took advantage of versioned transactional DDL to persist arbitrary data structures on the fly.
- mmt 16y agoPostgres does it with versioning, as that's how everything is stored on disk. As I've seen DDL outside of a transaction take much longer than inside one, it may use locking there.
- ora600 16y agoCan you link to documentation of ALTER SCHEMA for Oracle that does transactional DDL? I don't think it exists.
- briansmith 16y agoYou are right. I think ALTER SCHEMA might not be in standard SQL.
- julius_geezer 16y agoOracle DDL occurs as transaction per command. CREATE TABLE A (b number); -- one transaction; INSERT INTO a(b) VALUES(1); CREATE TABLE c (d number); -- another transaction, commits the insert.