Sybase Interview Question:

When are stored procedures compiled in Sybase?

Submitted by: Administrator

Stored procedures are in a database as rows in sysprocedures, in the form of parse trees. They are later compiled into execution plans.

A stored procedures is compiled:
1. with the first EXECute, when the parse tree is read into cache
2. with every EXECute, if CREATE PROCEDURE included WITH RECOMPILE
3. with each EXECute specifying WITH RECOMPILE
4. if the plans in cache for the procedure are all in use by other processes
5. after a LOAD DATABASE, when all procedures in the database are recompiled
6. if a table referenced by the procedure can not be opened (using object id), when recompilation is done using the table's name 7. after a schema change in any referenced table, including:
1. CREATE INDEX or DROP INDEX to add/delete an index
2. ALTER TABLE to add a new column
3. sp_bindefault or sp_unbindefault to add/delete a default
4. sp_bindrule or sp_unbindrule to add/delete a rule
8. after EXECute sp_recompile on a referenced table, which increments sysobjects.schema and thus forces re-compilation
Submitted by: Administrator