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? |
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...