|
Hi all, I like the new CREATE OR REPLACE clause for views, procedures and the like introduced with SA 11.0.1. My thought was that in case of an already existing object, the behaviour would be identical to an ALTER statement. I just found out that this seems not to be the case: Whereas an ALTER VIEW will leave the table_id and object_id (and the creation_time in SYSOBJECT) unchanged, a replaced view gets new values there. So a CREATE OR REPLACE seems to be a DROP statement combined with a CREATE statement and regenerated permissions. Of course, the difference is somewhat marginal; however, I thought CREATE OR REPLACE would be a handy substitute to the following common script construct: Is this difference by design? And are there other slight differences between CREATE OR REPLACE and ALTER with respect to existing objects? |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 19 Mar '10, 08:56
question was seen: 4,080 times
last updated: 15 Jan '15, 03:40
We already have ALTER so why create another syntactic representation for it? DROP and re-CREATE is an extremely common requirement, completely different from ALTER (albeit less different for some object types like VIEW)... it doesn't regenerate permissions, does it? Hope not, I want to use this construction in "recreate objects from scratch" scenarios.
CREATE OR REPLACE is my choice if I don't want to care if the object to be created exists already (no explicit DROP VIEW or DROP PROCEDURE). However up to now I've never bothered bout ID's
@Breck: From the docs: "Existing permissions are preserved when you use the OR REPLACE clause." AFAIk, that's the main difference (and advantage IMHO) between the new syntax and DROP/CREATE.
@Reimer: I normally don't care about IDs of database objects, either, but I often use "SELECT * from SYSTABLE ORDER BY ID" to check for newly generated tables/views (and would not expect those REPLACEd ones there), and I do care about the creation_time catalog entry.
Well, that old question is still unanswered...