DBA > Job Interview Questions > Sybase Interview Questions and Answers

When are stored procedures compiled in Sybase ?

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

When are stored procedures compiled in Sybase ?

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

(Continued on next question...)

Other Job Interview Questions