SQL Interview Questions and Answers

Question: What are stored procedures? How is it useful?

Answer: A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and saves the database the effort of parsing and compiling sql statements every time a query is run. Each database has its own stored procedure language, usually a variant of C with a SQL preproceesor. Newer versions of db's support writing stored procedures in Java and Perl too. Before the advent of 3-tier/n-tier architecture it was pretty common for stored procs to implement the business logic( A lot of systems still do it). The biggest advantage is of course speed. Also certain kind of data manipulations are not achieved in SQL. Stored procs provide a mechanism to do these manipulations. Stored procs are also useful when you want to do Batch updates/exports/houseKeeping kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.