SQL 2005 EXAM PRACTICE QUESTIONS:

Q.1) The Cursor Stored Procedures are......... - 2 true choices. A. sp_describe_column (Your Answer) B. sp_indexs C. sp_primarykeys D. sp_cursor_list (Your Answer) E. A and D Q.2) All information  about tables in user Database  is stored in a set of tables called the System catalog.that can be accessed using........ - 1 true choice. A. Sercurity Stored Procedures B. Catalog Stored Procedures (Your Answer) C. Distributed Query Stored Procedures D. A and B E. User-define Stored Procedures Q.3) Where are Catalog Stored Procedures ? -2 true choices A. sp_column (Your Answer) B. sp_addalias C. sp_describe_cursor D. sp_database and sp_statistics (Your Answer) E. A and C Q.4) The Stored procedures use to manage the security of the database. They are: -1 true choice. A. Database Mail and SQL mail stored Procedures B. Cursor Stored Procedures. C. Sercurity Stored Procedures (Your Answer) D. Database Mail and SQL mail stored Procedures. E. Distributed Query Stored Procedures Q.5) Database Mail and SQL mail stored Procedures. - 1 true choice. A. Used to pefrom email operations from with in the SQL server. (Your Answer) B. Use in the management of distributed queries. C. Use to implement the fuctionality of a Cursor. D. Use to manage the security of the DB. E. Send mail to the SQL server. Q.6) The Stored Procedure  - 2 true choices. A. is a set of pre-compiled T-SQL statements executed as a single unit. B. is a set of T-SQL that are executed as a single block of code that performs a specific task. (Your Answer) C. is used in database Administrative and information activities. D. can be created to carry out repetitive (Your Answer) E. All of about. Q.7) The ........is used to change the owner of the current database. -1 true choice. A. sp_cursor_list B. sp_changedbowner (Your Answer) C. sp_addalias D. sp_describe_column E. A and D Q.8) Advantages of The Store Procedures are: A. Reduced client/server traffic(Your Answer) B. can be created to carry out repetitive C. Reuse of code. (Your Answer) D. A and B E. B and D Q.9) Which  are not System Stored Procedures? -2 true choices. A. Distributed Query Stored Procedures B. Extended Stored Procedures (Your Answer) C. Temporary Stored Procedures (Your Answer) D. Database Mail and SQL mail stored Procedures. E. Cursor Stored Procedures Q.10) The Stored procedures  are created for temporary use with a session are called... - 1 true choice. A. Local Temporary Procedures (Your Answer) B. Global Temporary Procedures C. Remote Stored Procedures D. Temporary Stored Procedures (Correct Answer) E. Customer Stored Procedures Q.11) The..............are used to perform tasks that are unable to be perform using standard T-SQL statement. - 1 true choice A. Extended Stored Procedures. (Your Answer) B. T-SQL Stored Procedures C. Local stored Procedures D. B and C E. Not of about Correct Q.12) The................are not residents of SQL server. they are implemented as Dynamic Link Libraries(DLL) executed outsite the SQL Sever Environment. - 1 true choice. A. Remote Stored Procedures B. Temporary Stored Procedures C. Extended Stored Procedures. (Your Answer) D. B and C E. Customer Stored Procedures Q.13) The ...................are created individual user databases.And  can not be accessed by any user other than the one who has created it. - 1 true choice A. Extended Stored Procedures B. Temporary Stored Procedures C. Local stored Procedures (Correct Answer) D. Global Temporary Procedures E. B and C Q.14) The Local Temporary Procedures are............ - 2 true choices. A. Visible only to the user that create them. B. visible for all users C. use # prefix before the procedure name  D. use ## E. can be used any user Q.15) The Global Tem Procedures  .... - 3 true choices A. are dropped at the end of the last session.  B. are dropped at the end of the current session C. Can only be used by its owner D. can be used any user E. are visible for all users  Q.16) Temporary Stored Procedures include: - 2 true choices A. Local Temporary Procedures  B. Global Temporary Procedures C. Local stored Procedures D. Customer Stored Procedures E. A and C Q.17) EXECUTE xp_fileexists 'C:\sample.txt'  - 1 true choice. A. check file sample.txt exists. B. check file sample.txt exists or not (Correct Answer) C. Execute a file is named sample.txt D. A and C E. Not all about Q.18) OBJECT_DEFINITION()  - 2 choices A. system stored procedure use to display the dependencies of a stored procedure B. system function used to display the definition of a stored procedure.  C. system view used to display definition of a stored procedure. D. system function used to display the definition of a stored procedure by specifying the object ID of the procedure. E. A and D Q.19) The...............create stored procedures for performance of various tasks, they are referred  to as user-defined or custom stored procedures and can be set to override the default system procedures. - 1 true choice. A. Extended Stored Procedures B. Customer Stored Procedures (Correct Answer) C. T-SQL Stored Procedures D. System Stored Procedures E. A and D Q.20) Using "OUTPUT" clause. - 2 true choice A. Return information from each row on which the INSERT, UPDATE, and DELETE have been executed. B. Create stored procedures for performance of various tasks, they are referred to as user-defined or custom stored procedures C. Useful to retrieve the value of an identity or computed column after an INSERT or UPDATE operation  D. Print out a Variable. E. E and C Q.21) when a local temporary table is created inside a stored procedures, the table disappears when the procedure is exited. A. False B. True (Correct Answer) Q.22) System stored procedure used to display the definition of a stored procedure? -1 true choice. A. Object_definition() B. sp_helptext (Correct Answer) C. sp_depends D. sys.sql_modules E. sp_display Q.23) A Stored Procedure can reference tables,views, user-define functions and other A. True (Correct Answer) B. False Q.24) The permission associated with the stored procedure are not lost when a store procedure is re-created. And when a stored procedure is altered, the permissions defined for the stored procedure remain the same even though the procedure definetion is changeed A. true B. false (Correct Answer) Q.25) Everyone can  modify or rename all procedure. A. true B. false (Correct Answer) Q.26) A Stored procedure can be modified to change the procedure name as well as the procedure definition. A. True (Correct Answer) B. False Q.27) Stored procedures can be dropped if they are no longer needed. if another stored procedure calls a deleted procedure, - 1 choice. A. a replaced procedure is dipslay. B. an error message is display. (Correct Answer) C. No procedure display D. B and C E. none about Q.28) Before dropping a procedure, execute the .......................... system stored procedure to determine which objects depend on the procedure - 1 choice A. sys.sql_modules B. sp_depends (Correct Answer) C. sp_helptext D. sp_check E. A and B Q.29) The RETURN statement passes control back to the calling program. any T-SQL statements following the RETURN statement are  executed. A. True B. False (Correct Answer) Q.30) if a new procedure is created using the same name as well as the same parameters as the drop procedure,all calls to the dropped procedure will be  - 1 choice A. Denied immediately B. executed sucessfully (Correct Answer) C. Had some error messages D. No Action E. None about Unanswered Q.31) If a stored  procedure is executed sucessfully, It returns ...........by defaul. If errors are encountered and the procedure is not successfully executed, ...............interger value is returned. - 1 choice A. a values zero ....................a non - one B. a non-zero .................a zero C. a non-zero ........... a greater than zero D. a values zero...................a non-zero (Correct Answer) E. A and D Q.32) When a stored procedure is created using options, these options should be included in the ALTER PROCEDURE statement to retain their function. A. false B. True (Correct Answer) Q.33) .....................are defined at the time of creation of procedure. - 2 choice A. Input parameters B. Output parameters C. A and B D. Name of stored procedure Q.34) Which statement is true? - 1 choice. A. If the OUPUT key word is omitted, the procedure is still exceuted. B. If the OUPUT key word is omitted, the procedure is still exceuted but is not return a value.(Correct Answer) C. If the OUPUT key word is omitted, the procedure is not exceuted but is not return a value. D. A and B E. If the OUPUT key word is omitted, the procedure is still exceuted and return a value. Q.35) Which statement is true? - 1 choice A. When the RETURN statement is used in a stored procedure, It can return a null value. if a procedure tries to return a null value, a warning message is generated and the value zero is returned. B. When the RETURN statement is used in a stored procedure, It can not return a null value. if a procedure tries to return a null value, a warning message is generated and the value zero is returned. (Correct Answer) C. When the RETURN statement is used in a stored procedure, It can not return a null value. if a procedure tries to return a null value, a warning message is generated and the value one is returned. D. None above E. A and C Unanswered Q.36) Which statements are true? - 2 choices A. The OUTPUT parameters can be of TEXT and IMAGE data type. B. The calling statement must contain a variable to receive the return value C. The variable can be used in subsequent T-SQL statements in the batch or the calling procedure. D. Output parameter can not be cursor placeholders Q.37) Displays the default error message for an error. -1 choice. A. MESSAGE_ERROR() B. RETURN_ERROR() C. ERROR_MESSAGE() (Correct Answer) D. @@ERROR E. @@ERROR_LINE Q.38) Which statement are false? -2 choice A. The stored procedure can not be nested B. The maximum level of nesting is 32 C. There is no limit as to the number of stored procedure that can be called from a given stored procedure  D. None above Q.39) Specifies an integer value to be returned though the stored procedure. - 1 choice A. VALUE_RETURN() B. RETURN() C. RETURN (Correct Answer) D. RETURN_INT() Q.40) Which statement is not True? - 2 choice A. When stored procedure is executed from other stored procedure, the procedures are said to be nested. B. When an error occurs in the TRY block, the statements following the statement that caused the error are executed only after CATCH block is executed.  C. When the @@ERROR funtion is called, an error message is returned for the error occuring in the last executed statement. D. A and C Q.41)  Which statements are true? - 3 choices A. Trigger can not be executed directly nor do they pass or receive parameters B. A Trigger is executed when the table is created. C. Trigger is a stored procedure that executed when data in a specified table is modified. D. Trigger is the same as Check constraint. E. Trigger are often created to enforce referential integrity among logically related data in different table. Q.42) which are true? - 3 choice A. @@ERROR_LINE : Returns the line number that caused the error B. @@ERROR : Returns the error number for the error in the last T_SQL statment. C. @NESTLEVEL : Specifies the level of nesting of the current procedure. D. The RETURN funtion specifies the return value for a stored procedure E. The return code indicates the execution status of the stored procedure Q.43) The DDL Triggers.... A. are used to check and control database operations. B. are used to enforce business rules when data is modified in tables or views. C. are defined at the database level. D. execute either while modifying the data or after the data is modified. E. operate only after the table or view is modified.and are defined either at the database or the server level  Q.44) DML Trigger is executed when .... - 1 choice A. a session is established with a LOGON event. B. data is inserted,modified or delete in a table or a View using the INSERT,UPDATE or DELETE statements(Correct Answer) C. a table or a view is created, modified, or delete using the CREATE,ALTER, or DROP statements. Q.45) The OUTPUT keyword specifies that the variables are involved in passing values from the called procedure to the callling program. A. True (Correct Answer) B. False Q.46) The UPDATE triggers are created either at the column level or at the table. A. True (Correct Answer) B. false Q.47) Which are true for DELETE Trigger? - 2 choices. A. The record is deleted from the trigger table and inserted in the inserted table B. The record is deleted from the trigger table and inserted in the deleted table C. the deleted record stored in the deleted table is copied back to the Trigger table. D. the deleted record stored in the deleted table is lost when Trigger is executed. Q.48) INSTEAD OF Triggers. - 2 choices A. is executed in place of the INSERT, UPDATE or DELETE operation.  B. can not be created on views, It is only on tables C. A tables or a view can have any INSTEAD OF trigger defined for INSERT, UPDATE, and DELETE. D. Are executed before constraint checks are performed on the table.and They executed after creating Inserted and Deleted tables,  Q.49) which statements are true? - 2 choices A. An AFTER trigger is executed on completion of DROP, ATLTER and CREATE. B. AFTER triggers can be created on tables and columns C. A table can have multiple AFTER trigger defined for each INSERT, UPDATE, and DELETE operation. D. An AFTER trigger is executed before the constraint check in the table is completed E. The Trigger is executed after the inserted and Deleted tables are created Q.50) which statement is true? A. Update triggers do not use the Deleted table to update records in a table B. Delete trigger do not use the Inserted table to delete records from atable (Correct     Answer)